Преобразование чисел-как-текст в нормальные числа
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в 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 и скопируйте туда следующий код:
Sub Convert_Text_to_Numbers() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик - Макросы (Developer - Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) - и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
P.S.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Ссылки по теме
- Деление слипшегося текста по столбцам
- Вычисления без формул специальной вставкой
- Преобразование текста в числа с помощью надстройки PLEX
выделить столбец с данными, установить числовой формат и заменить (ctrl+h) запятую на запятую.
Еще как вариант для замены чисел из текстового формата в числовой использовать формулу =значен
Решили данную проблему тоже просто. Выделили саму эту "пустоту" мышкой, скопировали её и её же принудительно вставили в Заменить(CTRL+H) в поле "Найти:", а поле "Заменить на:" просто оставили пустым.
Испробовал много разных методов. Наилучший и самый быстрый результат дала процедура:
Очень нужная вещь на практике. Но
не совсем понимаюсовсем не понимаю как и что делает эта процедура. Был бы признателен за объяснение. Спасибо !Если тот работает, то им и пользуйтесь, а комментарии из "не работающего" читайте чтобы разобраться.
мозги пудритев заблуждение вводите? Я, думая, что код без комментариев нормально работает, пишу комментарии, а потом вдруг выясняется, что и тот, и тот не работают?У всех работает, а у Вас нет? Мало вероятно. Вы нужный числовой формат перед применением макроса ячейкам придали?
Приведенные примеры просто супер! Но если в значении идет первым 0, то при преобразовании он удаляется, например при значение 0123 после преобразования останется 123, при этом ты можешь не знать сколько нулей должно было быть в начале и длина номера тоже может быть различной, т.е. просто посчитать недостающие символы и дополнить их нулями не получится....
Ребята, кто-нибудь знает как решить эту головоломку?
Не решили?
Если вам нужно, чтобы перед числом стояли нули, то это не число, а текст.
Ставьте текстовый формат для ячейки и вводите туда 0123 - и все будет ОК.
Пытаюсь преобразовать числа как текст в нормальные числа, всеми способами почти выдает укороченную комбинацию чисел с запятыми и плюсами в ней. Подскажите, почему такое может быть, пробелов вроде нет никаких с двух сторон. Спасибо.
Подскажите пожалуйста по чему не работает в обратную сторону, с выделенными ячейками, а только если выделить одну.
Включил в свой код. Огромное спасибо!
должно быть число 1111,50
С точкой всё понятно, а вот как убрать руб.?