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

Примечание: Этот код автоматизирует преобразование чисел в текст на всем компьютере.

Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19. Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99. Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function

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

Использование функции SpellNumber в отдельных ячейках

1. Введите формулу =SpellNumber(A1)в ячейку, в которой нужно отобразить записанное число, где A1 — это ячейка с числом, преобразуемом в ячейку. Вы также можете ввести значение вручную, например =SpellNumber(22,50).
2. Нажмите ввод, чтобы подтвердить формулу.

Сохранение книги с функцией SpellNumber

В Excel не удается сохранить книгу с функциями макроса в стандартном формате книги без макроса (XLSX). Если нажать кнопку «>сохранить». Откроется диалоговое окно проекта VB. щелкните Нет.

Вы можете сохранить файл как книгу Excel Macro-Enabled (XLSM), чтобы сохранить его в текущем формате.

1. На вкладке Файл выберите команду Сохранить как.
2. В меню «Тип сохранения» выберите пункт «Macro-Enabled Excel».
3. Нажмите кнопку Сохранить.

Преобразование чисел из текстового формата в числовой

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

В большинстве случаев Excel распознает это, и вы увидите оповещение рядом с ячейкой, в которой числа хранятся в виде текста. Если вы видите оповещение:

Выберите ячейки, которые требуется преобразовать, а затем выберите .

В меню выберите Преобразовать в число .

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

Дополнительные сведения о форматировании чисел и текста в Excel см. в статье Форматирование чисел и текста.

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

1. В меню Excel выберите пункт Параметры.
2. В разделе Формулы и списки нажмите кнопку Проверка ошибок, а затем проверка флажок Включить проверка проверки ошибок в фоновом режиме.

Другие способы преобразования

Использование формулы

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

Вставка нового столбца

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

VALUE

Затем заполните формулу ячейки вниз, в другие ячейки. Для этого наведите курсор на правый нижний угол ячейки, пока он не изменится на знак «плюс».
Щелкните и перетащите вниз

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

Для этого выполните указанные ниже действия.

1. Выделите ячейки с помощью новой формулы.
2. Нажмите клавиши CTRL+C. Щелкните первую ячейку исходного столбца.
3. На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения
   . или используйте сочетание клавиш CTRL + SHIFT + V.

Преобразование текстового столбца в числа

1. Выбор столбца
   Выберите столбец с этой проблемой. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если эта проблема возникла в нескольких столбцах, см. раздел Использование специальной вставки и умножения ниже.)

Выделение текста в столбцы
Кнопка Текст в столбцы обычно используется для разделения столбца, но она также может использоваться для преобразования одного столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.

Использование специальной вставки и умножения

Если описанные выше действия не сработали, можно использовать этот метод, который можно использовать, если вы пытаетесь преобразовать несколько столбцов текста.

1. Выберите пустую ячейку без этой проблемы, введите в нее число 1 и нажмите клавишу ВВОД.
2. Нажмите клавиши CTRL+C , чтобы скопировать ячейку.
3. Выделите ячейки с числами, которые сохранены как текст.
4. На вкладке Главная выберите Вставить >Специальная вставка.
5. Выберите Умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.

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

Использование формулы

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

Вставка нового столбца

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

VALUE

Затем заполните формулу ячейки вниз, в другие ячейки. Для этого наведите курсор на правый нижний угол ячейки, пока он не изменится на знак «плюс».
Щелкните и перетащите вниз

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

Для этого выполните указанные ниже действия.

1. Выделите ячейки с помощью новой формулы.
2. Нажмите клавиши CTRL+ C. Щелкните первую ячейку исходного столбца.
3. На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения.
   или используйте сочетание клавиш CTRL+ SHIFT + V.

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

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