Число сохранено как текст эксель

1

Число сохранено как текст эксель

= Мир MS Excel/Статьи об Excel

• 1
• 2
• 3

Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.

В этой статье рассматриваются причины появления таких проблем и различные способы их устранения

Причина первая . Число сохранено как текст

В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

• С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки ( зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число

• С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами — нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) — в поле Найти вводим , (запятую) — в поле Заменить на тоже вводим , (запятую) — Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 — Enter

Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.

Кроме того, сторонние программы могут выгружать числа с точкой в качестве десятичного разделителя, тогда поможет замена точки на запятую.

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

• С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку — выполняем команду Копировать — выделяем диапазон с проблемными числами — Специальная вставка — Сложить — ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

• С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные — Текст по столбцам — Готово

• С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных

• С помощью макросов. Собственно, любой из перечисленных способов можно выполнить макросом. Если Вам приходится часто выполнять подобное преобразование, то имеет смысл написать макрос и запускать его по мере необходимости.

Приведу два примера макросов:

1) умножение на 1

2) текст по столбцам

Причина вторая . В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;" ";"")

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);"")

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);"");" ";"")

Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек

Преобразование чисел-как-текст в нормальные числа

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:

Причем иногда такой индикатор не появляется (что гораздо хуже).

В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

• перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
  
• функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
  
• при фильтрации псевдочисла отбираются ошибочно
• многие другие функции Excel также перестают нормально работать:
• и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

Возможно, «это не баг, а фича», конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию — один из них вам обязательно поможет.

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

По-сути, мы выполняем то же самое, что и в прошлом способе — умножение содержимого ячеек на единицу — но не формулами, а напрямую из буфера.

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Excel как сохранить число как текст

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Если вы хотите, чтобы в Excel числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, если вы используете номера кредитных карт или другие коды, содержащие 16 цифр или более, необходимо использовать текстовый формат. Это обусловлено тем, что Excel имеет максимум 15 цифр и Округляет все числа, которые следуют за 15 цифрами до нуля, что может быть не так, как вы хотите.

Если число имеет текстовый формат, это легко определить, поскольку число будет выровнено в ячейке по левому краю, а не по правому.

Выделите ячейку или диапазон ячеек, содержащих числа, которые нужно отформатировать как текст. Выделение ячеек и диапазонов.

Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.

На вкладке Главная в группе число щелкните стрелку рядом с полем числовой формат и выберите пункт текст.

Примечание: Если вы не видите параметр текст , прокрутите список до конца списка с помощью полосы прокрутки.

Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.

При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.

Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.

Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции текст можно найти в разделе функция текст.

Excel как сохранить число как текст

Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.

В этой статье рассматриваются причины появления таких проблем и различные способы их устранения

Причина первая . Число сохранено как текст

В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

• С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки ( зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число

• С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами — нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) — в поле Найти вводим , (запятую) — в поле Заменить на тоже вводим , (запятую) — Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 — Enter

Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.

Кроме того, сторонние программы могут выгружать числа с точкой в качестве десятичного разделителя, тогда поможет замена точки на запятую.

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

• С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку — выполняем команду Копировать — выделяем диапазон с проблемными числами — Специальная вставка — Сложить — ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

• С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные — Текст по столбцам — Готово

• С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных

• С помощью макросов. Собственно, любой из перечисленных способов можно выполнить макросом. Если Вам приходится часто выполнять подобное преобразование, то имеет смысл написать макрос и запускать его по мере необходимости.

Приведу два примера макросов:

1) умножение на 1

2) текст по столбцам

Причина вторая . В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)

Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек

Как сохранить число в виде текста в Microsoft Excel 2013

Пошаговая инструкция

Введенный ноль в начале числа будет автоматически удален, так как в числовом формате числа не несет никакой информации. Но бывают случаи, когда необходимо сохранить число без корректировок. В этом пошаговом руководстве я покажу Вам несколько способов, как сохранить число в виде текста в Microsoft Excel 2013.

Даже расширив границы ячейки Вы увидите, что введенный ноль в начале числа удален программой.

Первый способ заключается в том, что необходимо поставить апостроф в начале числа, который дает возможность сохранения числа в текстовом формате. Теперь кликнув на ячейку с числом в строке формул Вы увидите апостроф, который отсутствует в самой ячейке с числом. А восклицательный знак «Внимание» показывает, что содержимое ячейки сохранено как текст.

Второй способ состоит в том, что необходимо выделить ячейку, в которую будет вводиться число. Затем в пункте «Число» необходимо кликнуть на пиктограмму возле строки «Числовой формат» и в перечне выбрать формат «Текстовый».

Введенное число будет сохранено как текст и будет отображать ноль в начале числа, а в начале ячейки появится закрашенный треугольник, указывающий, что число сохранено в текстовом формате.

И третий способ, в котором необходимо кликнуть правой кнопкой мыши по ячейке, в которую Вы хотите ввести число. Затем в открывшемся окошке кликнем на строку «Формат ячеек…».

В диалоговом окне «Формат ячеек» во вкладке «Число» выбираем формат «Текстовый» и жмем кнопку «Ок».

Число сохранено как текст, а в строке формул Вы видите только введенное число без апострофа.

Если Вам необходимо ввести числа через значок «слэш», то программа автоматически сохранит содержимое ячейки как дату.

Для того, чтобы сохранить числа введенные через значок «слеш» необходимо аналогично поставить апостроф перед введенными числами или изменить формат ячейки на текстовый. Если информация помогла Вам – жмите Спасибо!

Число сохранено как текст Excel

Описание проблемы

Часто мы копируем данные в excel из интернета или текстовых редакторов (word). В результате этого сталкиваемся с проблемой, что некоторые числа вставились как текст, или ячейки с датами преобразовались в числовые значения. Сохраненные как текст числа не участвуют в расчетах и приводят к серьезным математическим ошибкам. Не сходится сумма ячеек, посчитанная в excel со значением на калькуляторе. Хуже всего то, что найти какое же число сохранено как текст довольно проблематично (особенно если в файле не одна тысяча ячеек с данными). Надстройка VBA-Excel поможет быстро найти ошибку.

Решение проблемы

Решение задачи довольно простое — залить все ячейки заливкой в соответствии с типом данных в них. Т.е. числовые значения одним цветом, текстовые вторым и так далее (даты, формулы, логические выражения, ошибки). Для этого понадобится:

1. Выделить диапазон, в котором необходимо построить цветовую схему данных
2. Перейти на вкладку VBA-Excel
3. В меню Подсветка выбрать команду Цветовая карта данных

Получаем следующую картину.

Наглядно видно, что цвет четырех ячеек с числами отличается от остальных. Цвет заливки говорит нам, что формат данных ячеек текстовый. Это приводило к тому, что они игнорировались при суммировании.

После того, как ошибки будут исправлены, отключите цветовую карту данных нажав на соответствующую команду в меню Подсветка.

Преобразование текста в число в ячейке Excel

При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.

Как преобразовать текст в число в Excel

Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.

Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.

Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.

1. Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число».
2. Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
3. Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой.
4. Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой.
5. Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее». На третьем шаге обращаем внимание на формат данных столбца.

Последний способ подходит в том случае, если значения находятся в одном столбце.

Макрос «Текст – число»

Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.

Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.

Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.

Почему не суммируется (число сохраняет как текст) в Excel

Многие пользователи сталкиваются с проблемой, почему при попытках суммировать число, у них этого не получается сделать. Что же, давайте разберемся в причинах этой проблемы более детально. Чаще всего это происходит по причине того, что число было сохранено в текстовом формате. Сегодня мы найдем причины этого явления, а также научимся решать ее разными методами.

Возможные причины, почему не считается сумма

Очень часто сумма не хочет считаться после того, как в Excel были скопированы данные из других программ. И в ходе использования этой информации обнаруживается, что числа не получается суммировать, а между датами не получается понять, сколько прошло дней.

Число сохранено, как текст

Как можно понять, что число было сохранено в текстовом формате? Чтобы сделать это, нужно посмотреть, к какому краю число было прижато. Кроме этого, при импортировании данных может показываться зеленый треугольник, который сигнализирует об ошибке перевода данных в правильный формат. Если навести на него мышью, то он и покажет, что число было записано в текстовом формате.

1

Способы решения проблемы

При этом если попытаться изменить формат ячейки с помощью стандартных средств Excel, то ничего не получится. Правда, если поставить курсор ввода текста в поле ввода формулы, после чего нажать кнопку «Enter», то проблема решается. Но очевидно. что это очень неудобный метод, когда речь идет об огромном количестве ячеек.

Правда, есть много других способов, как выкрутиться из этой ситуации.

Маркер ошибки и тег

Прежде всего, можно воспользоваться непосредственно маркером, сигнализирующем об ошибке. Если есть на ячейке тег зеленого цвета, то нажав по нему, появляется возможность сразу превратить ее в текстовый формат. Для этого в появившемся меню нажимаем на «Преобразовать в число».

Операция Найти/Заменить

Еще один метод решения ситуации, при которой числа записываются в текстовом формате – использовать операцию «Найти/заменить». Допустим, в каких-то ячейках содержится число, имеющее десятичную запятую, сохраненные в текстовом формате. Для этого нужно нажать соответствующую кнопку на ленте или в верхнем меню (в зависимости от используемой версии Excel). Появится окно, в котором нужно заменить запятую на саму себя. Да, в буквальном смысле, нужно в поле «Найти» ввести запятую и в поле «Заменить» также ввести запятую. После этого формат должен быть преобразован автоматически. В принципе, операция аналогична клику на строку формул и дальнейшему нажатию по кнопке ввода. Та же операция может быть и с датами, только нужно точку заменять на точку.

Если же данные были импортированы из других программ, то причина может быть еще и в разности форматов десятичных значений. Если в ячейке в качестве разделителя служит точка, а не запятая, то Эксель не будет эти данные распознавать, как числовые. В таком случае нужно заменить точку на запятую в соответствующих ячейках.

Специальная вставка

Использование «Специальной вставки» – это достаточно универсальный метод, поскольку позволяет превращать в формат чисел любые цифры, относящиеся к любому виду как дробному, так и целым числам. Также его можно использовать для того, чтобы переводить даты в соответствующий формат. Чтобы использовать эту функцию, необходимо найти любую пустую ячейку, выделить ее и скопировать ее. После этого нажимаем правой кнопкой мыши по любой ячейке, формат которой неправильный, после чего нажимаем кнопки «Специальная вставка» – «Сложить» – «ОК». Это аналогичная добавлению нуля операция. Значение ячейки не меняется абсолютно, но ее формат превращается в числовой. Также можно использовать умножение диапазона значений на единицу.

Инструмент Текст по столбцам

Этот инструмент наиболее удобно применять, если используется всего одна колонка. Если их больше, ничего страшного, но придется использовать его по отдельности для каждой колонки. Чтобы это сделать, нужно выделить соответствующий столбец, выставить числовой формат и выполнить команду «Текст по столбцам», которая находится во вкладке «Данные».

Формулы

Очень популярный способ решения проблем с отображением ячеек – использование функции ПОДСТАВИТЬ() , ЗНАЧЕН() и некоторыми другими формулами. Этот способ можно использовать, если есть возможность использовать дополнительные столбцы, в которые может вводиться формула. Можно использовать и другие математические операции, такие как двойной минус (—), добавление к числу нуля, умножение на единицу и любой другой подобной операции. После этого получившиеся ячейки копируются и вставляются в те места, в которых до этого были значения в текстовом формате.

Макросы

Отдельное внимание стоит уделить использованию макросов для исправления ошибок. В принципе, можно использовать любой предыдущий способ через макрос, поэтому мы его отдельно приведем. Достаточно просто написать соответствующий скрипт и выполнить его. А вот некоторые примеры, которые можно использовать.

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

На первый взгляд, ничего особенного. Но если человек хоть немного работал с текстами, он сразу поймет, что разбивка этого фрагмента на строки далека от удачной. Давайте посмотрим на него более внимательно. Например, инициалы и фамилия были разделены, что не очень хорошо. То же самое касается номера года и сокращенного обозначения года.

Также неудачно оказалось разделение фамилии «Палажченко» и обозначения его должности. В результате, выглядеть этот фрагмент текста стал, как прямая речь.

Простыми словами, в этом фрагменте содержатся куски, где следует использовать пробел. Но в результате этого пробела теряется аккуратность оформления. И чтобы этого добиться, нужно использовать символ неразрывного пробела. С его помощью мы также разделяем слова, но при этом они остаются на одной строке. Слова, разделенные им, воспринимаются программой, как одно цельное слово, но при этом выглядят, как несколько. Получается эдакий компромисс между тем, как отображается текст и как он воспринимается программой. То есть, если окажется, что нужно их переносить на новую строку, то переноситься будут они все. Их нужно применять в таких ситуациях:

1. Перед тире, которое находится посередине строки. Только в трех случаях допускается использование тире в начале строки: если используется прямая речь, если тире маркирует элемент списка и при условии, что тире заменяет прочерк. Чтобы тире не переносилось на новую строку, перед ним нужно поставить неразрывный пробел.
2. Между числом и единицей измерения. Довольно часто эта проблема случается. В результате человек может перенести в таблицу Excel число с неразрывным пробелом, а единица измерения окажется в другой ячейке. В целом, использовать в таком случае неразрывный пробел нужно, чтобы не разделять их. Но вот при переносе в Эксель могут возникнуть проблемы.
3. Перед знаком процента. В таком случае ячейка может не переводиться в процентный формат. Конечно, не везде можно встретить знак процента, отделенный от числа пробелом, но некоторые считают, что так правильно. Поэтому при переносе в Эксель данные могут отображаться в текстовом формате. То же касается и обычного пробела.
4. Знак номера и параграфа. Такая ситуация также часто возникает, когда приходится переносить разделы учебников или части документов в электронные таблицы.
5. Многозначные числа. Самая частая причина, почему неразрывные пробелы переносятся в таблицу Excel. По правилам в многозначных числах обязательно ставить пробелы, чтобы упростить их чтение пользователями. Но если число слишком большое, то оно может автоматически перенестись частями на следующую строку. Чтобы решить эту проблему, используется неразрывный пробел. И если такое число скопировать в ячейку, она будет автоматически отображаться в текстовом формате.

Как правило, неразрывные пробелы оказываются в Excel после того, как данные были перенесены из документа Word.

Возможности так легко обнаружить неразрывные пробелы средствами Excel нет. Но если скопировать содержимое ячейки в Word и включить опцию отображения непечатаемых символов, то можно увидеть своеобразные кружочки, которые похожи на знак градуса, только чуть большего размера. Это как раз и есть эти пробелы.

Этот символ есть в любом шрифте. Как правило, все программы, предназначенные для работы с текстом, правильно его обрабатывают. При этом в некоторых из них неразрывные пробелы одинакового размера. Из-за этого наблюдаются проблемы с отображением страницы по ширине, поскольку обычные и неразрывные пробелы имеют одинаковые размеры.

Может понадобиться вводить неразрывный пробел и в Excel. Чтобы это сделать, нужно нажать комбинацию клавиш ALT и 0160. Также в стандартной комплектации Windows предусмотрена комбинация горячих клавиш CTRL + SHIFT + ПРОБЕЛ, с помощью которой можно вводить неразрывный пробел почти в любой программе.

Существует два основных способа решения этой проблемы – воспользоваться функцией «Найти/Заменить» или же использовать формулу.

Операция Найти/Заменить

Чтобы убрать пробелы, можно воспользоваться функцией «Найти/Заменить». В первое поле нужно ввести знак пробела, в то время как нижнее поле оставляем пустым.

Важно убедиться, что там пробелов нет .

Формула

Использование формулы также возможно для удаления пробелов. В зависимости от того, пробел обычный или неразрывный, нужно использовать разные формулы. Также есть одна универсальная, которая позволяет убрать одновременно все пробелы, содержащиеся в ячейке.

Что это за формулы?

Во всех случаях используется функция ПОДСТАВИТЬ . Если нам нужно убрать обычный пробел, то используется следующая формула.

С помощью двойного знака минуса мы выполняем конвертацию текстового значения в числовое. Это эквивалент тому, что мы умножили получившееся число на -1, а потом получившееся отрицательное значение снова умножили на -1. В результате, ничего не изменилось, но благодаря выполненной математической операции ячейка автоматически сконвертирована в числовой формат.

В случае с неразрывными пробелами формула будет такой.

Как видим, здесь в качестве второго аргумента мы используем код символа. А с помощью этой формулы можно убрать как обычные, так и неразрывные пробелы.

Иногда проблема оказывается намного сложнее, чем может показаться на первый взгляд. Поэтому в ряде случаев приходится комбинировать все описанные выше методы.

Выводы

Таким образом, проблема, почему не получается суммировать несколько чисел, оказывается не такой сложной, как может показаться на первый взгляд. Решить ее очень просто, достаточно просто знать некоторые функции Excel.