Excel число сохранено как текст как исправить
Информация о сайте
Инструменты и настройки
Excel Windows
и
Excel Macintosh
Работа форума и сайта
Функции листа Excel
= Мир MS Excel/Статьи об Excel
Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.
В этой статье рассматриваются причины появления таких проблем и различные способы их устранения
В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.
Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.
Есть несколько способов решения данной проблемы
Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.
Кроме того, сторонние программы могут выгружать числа с точкой в качестве десятичного разделителя, тогда поможет замена точки на запятую.
Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()
Вариантом этого приёма может быть умножение диапазона на 1
Приведу два примера макросов:
2) текст по столбцам
Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.
Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).
Пробелы можно удалить и формулой. Варианты:
Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)
Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)
Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)
Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек
Форматирование чисел в виде текста
Если вы хотите, чтобы в Excel числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, при использовании номеров кредитных карт или других числового кода, содержащих не менее 16 цифр, необходимо использовать текстовый формат. Это происходит потому Excel что точность не может быть больше 15 цифр, и она округлит все числа после 15-й цифры до нуля, что, вероятно, вас не захотнет.
Если число имеет текстовый формат, это легко определить, поскольку число будет выровнено в ячейке по левому краю, а не по правому.
Выберем ячейку или диапазон, которые содержат числа, которые вы хотите отформать как текст. Выбор ячеек или диапазона.
Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.
На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовом формате и выберите текст.
Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.
Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.
При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.
Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.
Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции ТЕКСТ см. в документе Функция ТЕКСТ.
Почему не суммируется (число сохраняет как текст) в Excel
Многие пользователи сталкиваются с проблемой, почему при попытках суммировать число, у них этого не получается сделать. Что же, давайте разберемся в причинах этой проблемы более детально. Чаще всего это происходит по причине того, что число было сохранено в текстовом формате. Сегодня мы найдем причины этого явления, а также научимся решать ее разными методами.
Возможные причины, почему не считается сумма
Очень часто сумма не хочет считаться после того, как в Excel были скопированы данные из других программ. И в ходе использования этой информации обнаруживается, что числа не получается суммировать, а между датами не получается понять, сколько прошло дней.
Число сохранено, как текст
Как можно понять, что число было сохранено в текстовом формате? Чтобы сделать это, нужно посмотреть, к какому краю число было прижато. Кроме этого, при импортировании данных может показываться зеленый треугольник, который сигнализирует об ошибке перевода данных в правильный формат. Если навести на него мышью, то он и покажет, что число было записано в текстовом формате.
1
Способы решения проблемы
При этом если попытаться изменить формат ячейки с помощью стандартных средств Excel, то ничего не получится. Правда, если поставить курсор ввода текста в поле ввода формулы, после чего нажать кнопку «Enter», то проблема решается. Но очевидно. что это очень неудобный метод, когда речь идет об огромном количестве ячеек.
Правда, есть много других способов, как выкрутиться из этой ситуации.
Маркер ошибки и тег
Прежде всего, можно воспользоваться непосредственно маркером, сигнализирующем об ошибке. Если есть на ячейке тег зеленого цвета, то нажав по нему, появляется возможность сразу превратить ее в текстовый формат. Для этого в появившемся меню нажимаем на «Преобразовать в число».
Операция Найти/Заменить
Еще один метод решения ситуации, при которой числа записываются в текстовом формате – использовать операцию «Найти/заменить». Допустим, в каких-то ячейках содержится число, имеющее десятичную запятую, сохраненные в текстовом формате. Для этого нужно нажать соответствующую кнопку на ленте или в верхнем меню (в зависимости от используемой версии Excel). Появится окно, в котором нужно заменить запятую на саму себя. Да, в буквальном смысле, нужно в поле «Найти» ввести запятую и в поле «Заменить» также ввести запятую. После этого формат должен быть преобразован автоматически. В принципе, операция аналогична клику на строку формул и дальнейшему нажатию по кнопке ввода. Та же операция может быть и с датами, только нужно точку заменять на точку.
2
Если же данные были импортированы из других программ, то причина может быть еще и в разности форматов десятичных значений. Если в ячейке в качестве разделителя служит точка, а не запятая, то Эксель не будет эти данные распознавать, как числовые. В таком случае нужно заменить точку на запятую в соответствующих ячейках.
3
Специальная вставка
Использование «Специальной вставки» – это достаточно универсальный метод, поскольку позволяет превращать в формат чисел любые цифры, относящиеся к любому виду как дробному, так и целым числам. Также его можно использовать для того, чтобы переводить даты в соответствующий формат. Чтобы использовать эту функцию, необходимо найти любую пустую ячейку, выделить ее и скопировать ее. После этого нажимаем правой кнопкой мыши по любой ячейке, формат которой неправильный, после чего нажимаем кнопки «Специальная вставка» – «Сложить» – «ОК». Это аналогичная добавлению нуля операция. Значение ячейки не меняется абсолютно, но ее формат превращается в числовой. Также можно использовать умножение диапазона значений на единицу.
4
Инструмент Текст по столбцам
Этот инструмент наиболее удобно применять, если используется всего одна колонка. Если их больше, ничего страшного, но придется использовать его по отдельности для каждой колонки. Чтобы это сделать, нужно выделить соответствующий столбец, выставить числовой формат и выполнить команду «Текст по столбцам», которая находится во вкладке «Данные».
5
Формулы
Макросы
Отдельное внимание стоит уделить использованию макросов для исправления ошибок. В принципе, можно использовать любой предыдущий способ через макрос, поэтому мы его отдельно приведем. Достаточно просто написать соответствующий скрипт и выполнить его. А вот некоторые примеры, которые можно использовать.
Sub conv()
Dim c As Range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = c.Value * 1
c.NumberFormat = “#,##0.00”
End If
Next
End Sub
Этот код умножает текстовое значение на единицу.
Sub conv1()
Selection.TextToColumns
Selection.NumberFormat = “#,##0.00”
End Sub
А это код, демонстрирующий использование инструмента «Текст по столбцам».
В записи числа имеются посторонние символы
Способы решения проблемы
Также частой причиной, почему начинают отображаться числа в виде текста, является появление в соответствующих ячейках невидимых символов. Наиболее часто ими служат пробелы, которые могут находиться в каком-угодно месте, начиная началом и концом числа и заканчивая использованием их для отделения разрядов друг от друга.
Еще один тип пробелов, который может мешать сохранению ячейки в правильном формате – это так называемый неразрывный пробел (имеющий код 160). Описанного выше способа для решения этой проблемы недостаточно. Чтобы решить возникшую проблему, необходимо скопировать этот символ непосредственно с ячейки, а потом вставить в поле «Найти» или же набрать в этом поле комбинацию Alt + 0160 (но на ноутбуках такой номер не получится, потому что требуется цифровая клавиатура для выполнения этой задачи).
Для обычного человека вообще не всегда понятно, что такое неразрывный пробел и где он может использоваться. Чтобы стало более понятно, давайте посмотрим на этот текст.
6
На первый взгляд, ничего особенного. Но если человек хоть немного работал с текстами, он сразу поймет, что разбивка этого фрагмента на строки далека от удачной. Давайте посмотрим на него более внимательно. Например, инициалы и фамилия были разделены, что не очень хорошо. То же самое касается номера года и сокращенного обозначения года.
Также неудачно оказалось разделение фамилии «Палажченко» и обозначения его должности. В результате, выглядеть этот фрагмент текста стал, как прямая речь.
Простыми словами, в этом фрагменте содержатся куски, где следует использовать пробел. Но в результате этого пробела теряется аккуратность оформления. И чтобы этого добиться, нужно использовать символ неразрывного пробела. С его помощью мы также разделяем слова, но при этом они остаются на одной строке. Слова, разделенные им, воспринимаются программой, как одно цельное слово, но при этом выглядят, как несколько. Получается эдакий компромисс между тем, как отображается текст и как он воспринимается программой. То есть, если окажется, что нужно их переносить на новую строку, то переноситься будут они все. Их нужно применять в таких ситуациях:
Как правило, неразрывные пробелы оказываются в Excel после того, как данные были перенесены из документа Word.
Возможности так легко обнаружить неразрывные пробелы средствами Excel нет. Но если скопировать содержимое ячейки в Word и включить опцию отображения непечатаемых символов, то можно увидеть своеобразные кружочки, которые похожи на знак градуса, только чуть большего размера. Это как раз и есть эти пробелы.
Этот символ есть в любом шрифте. Как правило, все программы, предназначенные для работы с текстом, правильно его обрабатывают. При этом в некоторых из них неразрывные пробелы одинакового размера. Из-за этого наблюдаются проблемы с отображением страницы по ширине, поскольку обычные и неразрывные пробелы имеют одинаковые размеры.
Может понадобиться вводить неразрывный пробел и в Excel. Чтобы это сделать, нужно нажать комбинацию клавиш ALT и 0160. Также в стандартной комплектации Windows предусмотрена комбинация горячих клавиш CTRL + SHIFT + ПРОБЕЛ, с помощью которой можно вводить неразрывный пробел почти в любой программе.
Существует два основных способа решения этой проблемы – воспользоваться функцией «Найти/Заменить» или же использовать формулу.
Операция Найти/Заменить
Чтобы убрать пробелы, можно воспользоваться функцией «Найти/Заменить». В первое поле нужно ввести знак пробела, в то время как нижнее поле оставляем пустым.
Формула
Использование формулы также возможно для удаления пробелов. В зависимости от того, пробел обычный или неразрывный, нужно использовать разные формулы. Также есть одна универсальная, которая позволяет убрать одновременно все пробелы, содержащиеся в ячейке.
Что это за формулы?
В случае с неразрывными пробелами формула будет такой.
Как видим, здесь в качестве второго аргумента мы используем код символа. А с помощью этой формулы можно убрать как обычные, так и неразрывные пробелы.
Иногда проблема оказывается намного сложнее, чем может показаться на первый взгляд. Поэтому в ряде случаев приходится комбинировать все описанные выше методы.
Выводы
Таким образом, проблема, почему не получается суммировать несколько чисел, оказывается не такой сложной, как может показаться на первый взгляд. Решить ее очень просто, достаточно просто знать некоторые функции Excel.
Преобразование чисел-как-текст в нормальные числа
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже).
В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:
Способ 1. Зеленый уголок-индикатор
Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :
Все числа в выделенном диапазоне будут преобразованы в полноценные.
Способ 2. Повторный ввод
Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.
Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.
Способ 3. Формула
Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:
Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.
Способ 4. Специальная вставка
Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:
Способ 5. Текст по столбцам
После нажатия на Готово Excel преобразует наш текст в нормальные числа.
Способ 6. Макрос
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
Всем доброго времени суток!
Возникла такая проблема. После вывода результата примерно такого запроса к БД в грид:
И после экспорта из грида в ексель в каждой ячейке столбцов соответствующих PriceOrder, Discount, GPrice, RecMoney и DebtOnOrder появляется зеленый треугольник (число сохранено как текст). Как это можно исправить на уровне С#? В интернете искал, но что-то не нашел ничего кроме этого (что не помогло):
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Как автоматизировать поиск и устранение ошибки «число сохранено как текст»?
Есть столбец, в котором есть ячейки, содержащие численные значения, сохраненные как текст. Как.
Как задать массиву имя, которое сохранено в другом массиве
Есть вектор Х, состоящий из целых чисел. Есть куча двумерных массивов с именами y1,y2. yn. Каким.
а почему? )
Вот код сразу на 20 столбцов
Решение
я ошибся. надо вот так
Ничего не изменилось. Зеленые треугольники как были так и есть.
Добавлено через 3 минуты
вы попробуйте сперва поменять формат ячеек, а потом уже заполняйте его данными. Т.е. перенесите строку
Всем спасибо! Проблема решилась простым удалением функции Convert.ToString() в 18-й строке (здесь в 20-й):
Помощь в написании контрольных, курсовых и дипломных работ здесь.
как исправить текст
у меня такой вопрос,скачат хтмл+цсс шаблон,он на английском языке,стал менять контент на.
Как преобразовать число в текст (на подобии =текст() )
Нужно какую нибудь функцию VBA, которая бы выполняла то же самое что и =текст() (тесть.
Как исправить повреждённый текст в Word 2007
Подскажите, пожалуйста, кто знает, как исправить повреждённый текст в Word 2007, не могу найти эту.
Как исправить текст в модуле JA K2 FILTER AND SEARCH?
Доброго времени стук. Возник вопрос по модулю JA K2 FILTER AND SEARCH 1.0.4 (Joomla 2.5) а именно.