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

182988 13.08.2016 Скачать пример

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа 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'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме



21.08.2016 21:04:06
что-то макрос не работает - ничего не происходит со числами-текстом
21.08.2016 21:12:54
Макрос работает, если разделитель - "точка". Если "запятая", то - не работает
22.08.2016 11:48:48
Ещё способ, работающий при наличии одинаковых символов, например ",":
выделить столбец с данными, установить числовой формат и заменить (ctrl+h) запятую на запятую.
23.08.2016 09:16:03
Добрый день!
Еще как вариант для замены чисел из текстового формата в числовой использовать формулу =значен
10.10.2016 13:15:30
Буквально только что столкнулись с вариантом данных в Екселе, где текст, судя по всему, был скопирован из Ворда или почты, когда не сработал ни один из способов выше. Всему виной символ в конце каждого числа, визуально похожий на пробел, но как пробел не определяющийся. Т.е. даже заменить пробел на пустоту не получалось ни через CTRL+H, ни через функцию =ПОДСТАВИТЬ(ячейка;" ";"").

Решили данную проблему тоже просто. Выделили саму эту "пустоту" мышкой, скопировали её и её же принудительно вставили в Заменить(CTRL+H) в поле "Найти:", а поле "Заменить на:" просто оставили пустым.
05.03.2021 08:38:11
Гениально!  Мне только этот способ и помог!
04.11.2016 19:18:02
Много раз приходилось сталкиваться с кривым распознаванием чисел после экспорта из БД.
Испробовал много разных методов. Наилучший и самый быстрый результат дала процедура:
Sub Repair_Value()   ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа    
   Dim rArea As Range
   On Error Resume Next
   ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
   If Err Then Exit Sub
   With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
   For Each rArea In Selection.Areas
   rArea.FormulaLocal = rArea.FormulaLocal
   Next rArea
   With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
17.02.2017 15:16:51
Alex_ST

Очень нужная вещь на практике. Но не совсем понимаю совсем не понимаю как и что делает эта процедура. Был бы признателен за объяснение. Спасибо !
17.02.2017 21:19:35
Ну, примерно так:
 Sub Repair_Value()   ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа
   Dim rArea As Range
   On Error Resume Next ' обработчик ошибок
   'ActiveWindow.RangeSelection   ' диапазон выбранных ячеек листа даже если выбран графический объект
   ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select ' в выбранном диапазоне выделить ячейки с константами _
   так будет быстрее, чем обрабатывать все ячейки в Selection
   If Err Then Exit Sub ' если нужных ячек не оказалось, то их невозможно выбрать и будет ошибка
   With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With ' отключаем все замедляющие процедуры

   For Each rArea In Selection.Areas 'т.к. выбранными скорее всего окажутся не смежные ячейки, а области, то обрабатывать нужно каждую из областей
      rArea.FormulaLocal = rArea.FormulaLocal ' значения во всех ячейках области заменить на них же. При этом произойдёт обновление форматов (это такая не документированная особенность Excel)
   Next rArea
   With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With ' включаем все обратно в обычный режим.
End Sub
18.02.2017 20:28:34
Что-то не работает. Можно ли файл с примером. Спасибо заранее.
18.02.2017 22:17:34
Код процедуры ничем не отличается от того, что я приводил выше 04.11.2016 19:18:02 (если, конечно, не добавил опечаток, когда писал комментарии :) )
Если тот работает, то им и пользуйтесь, а комментарии из "не работающего" читайте чтобы разобраться.
19.02.2017 00:26:14
По-моему оба не работают.:oops: Файл с примером все бы прояснил ....
19.02.2017 16:22:45
Ну а что же Вы тогда мозги пудрите в заблуждение вводите? Я, думая, что код без комментариев нормально работает, пишу комментарии, а потом вдруг выясняется, что и тот, и тот  не работают?
У всех работает, а у Вас  нет? Мало вероятно. Вы нужный числовой формат перед применением макроса ячейкам придали?
09.12.2021 19:30:05
Ещё можно вот так.


Public Sub pChangeNumber()
    
    Dim r As Range
    
    With Application:
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    
    For Each r In Selection.Cells
        On Error GoTo ErrHandl
            r.Cells.Value2 = CLng(r.Cells.Value2)
    Next r
    
    Set r = Nothing
    
    With Application:
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
ErrHandl:
    With Application:
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    Set r = Nothing
    Exit Sub
    
End Sub
 
15.06.2018 08:55:25
Переделал для себя, не обрабатывает скрытые строки:

Sub Fix_Format()
    Dim rng As Range
    With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
    For Each rng In Selection
        If Rows(rng.row).Hidden = False Then
            rng.FormulaLocal = rng.FormulaLocal
            rng.NumberFormat = "DD.MM.YYYY"
        End If
    Next
    With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub 
24.04.2017 11:35:30
В моём случае было записано так 1 131 823,90 помогло только удаление пробелов. Функция в надстройке Plex (в значения) тоже не помогла.
01.11.2017 17:00:30
Спасибо,помог только ваш совет.Но не знаю,как в большой таблице,выгруженной из 1С,убрать сразу везде пробелы-разделители разрядов чисел...
11.08.2017 11:59:17
помогла формула =сжпробелы, ни один из указанных способов не срабатывал
11.08.2017 12:28:51
Видимо, перед или после чисел были пробелы - бывает.
03.10.2017 16:38:10
как вариант:

Sub Conv()
    With ActiveSheet.UsedRange
        .Replace ",","."        
        arr = .Value
        .NumberFormat = "General"
        .Value = arr
    End With
End Sub
 
06.11.2017 20:35:31
Доброе время суток!
Приведенные примеры просто супер! Но если в значении идет первым 0, то при преобразовании он удаляется, например при значение 0123 после преобразования останется 123, при этом ты можешь не знать сколько нулей должно было быть в начале и длина номера тоже может быть различной, т.е. просто посчитать недостающие символы и дополнить их нулями не получится....
Ребята, кто-нибудь знает как решить эту головоломку?:oops:
09.11.2017 10:24:39
А какое отношение имеет ваш вопрос к теме преобразования в числа, текстовых значений, похожих на числа? В математике ни одно число не начинается с нуля, чтоб за ним были значащие цифры. Вы хочете работать как раз с текстовыми значениями, которые похожи на числа и вам, если вы хочете нулей сначала, и не надо их преобразовывать в числа... Так что пользуйтесь текстовыми функциями, которые в екселе как раз таки есть.
22.11.2017 05:54:13
Доброе время суток!
Не решили?
22.11.2017 15:58:57
А что решать-то?
Если вам нужно, чтобы перед числом стояли нули, то это не число, а текст.
Ставьте текстовый формат для ячейки и вводите туда 0123 - и все будет ОК.
13.12.2017 22:10:53
Доброе время суток! Спасибо всем за комментарии, была проблема, что при выгрузки данных из одной системы реквизиты выгружались в числовом формате, терялись первые нолики... похоже нужно просто из системы данные поля выгружать в тестовом формате :(
09.10.2018 22:36:59
Добрый вечер!
Пытаюсь преобразовать числа как текст в нормальные числа, всеми способами почти выдает укороченную комбинацию чисел с запятыми и плюсами в ней. Подскажите, почему такое может быть, пробелов вроде нет никаких с двух сторон. Спасибо.
21.10.2018 19:39:45
Selection.Value = Selection.Value
Этот код обновляет данные, но текст в число не превращает. При этом, что формат - общий, запятые вместо точек, убраны пробелы и тд
14.11.2018 08:34:09
Наконец-то научилась преобразовывать числа с неправильными разделителями, спасибо!
03.04.2019 09:04:31
Добрый день.
Подскажите пожалуйста по чему не работает в обратную сторону, с выделенными ячейками, а только если выделить одну.

Sub Convert_Numbers_to_Text()
    Selection.NumberFormat = "@"
    Selection.Value = CStr(Selection.Value)
End Sub
30.04.2019 07:52:08
а как преобразовать в числа если формат Дата? а видимость как число с десят знаками. через зелененький треугольничек доступно только Преобразовать в дату типа и т д ......  помогите плиз, объем ячеек неимоверный
05.11.2019 11:03:20
Способ 6. Макрос - прекрасно помог!!!
Включил в свой код. Огромное спасибо!
19.04.2021 15:56:28
текст: 1111.50 руб.
должно быть число 1111,50
С точкой всё понятно, а вот как убрать руб.?
28.01.2022 18:52:59

Sub Convert_Text_to_Numbers()
    Selection = CDbl(Selection)
End Sub
23.12.2022 16:36:31
А если у меня таблицы по 10 000 ячеек числами в виде текста и листов в книге около 1000? Как одной командой преобразовать в числа все эти ячейки? Везде зеленые уголки-индикаторы...  
Наверх