В этом учебном пособии показано множество различных способов преобразования текста в числа в Excel: опция проверки ошибок чисел, формулы, математические операции, специальная вставка и многое другое.
Иногда значения на листах Excel выглядят как числа, но их нельзя складывать или умножать, что приводит к ошибкам в формулах. Распространенной причиной этого являются числа, записанные в виде текста. Во многих случаях Microsoft Excel достаточно умен, чтобы автоматически преобразовывать числовые символы, импортированные из других программ, в обычные числа. Но иногда числа остаются отформатированными как текст, вызывая множество проблем в ваших электронных таблицах.
Сортировка данных перестает работать корректно, потому что числовые и текстовые значения упорядочены по-разному. Функции поиска, такие как ВПР, также не находят нужных значений (подробнее об этом читайте — Почему не работает ВПР?). Подсчет с помощью функций СУММЕСЛИ и СЧЁТЕСЛИ даст неправильный результат. Если они находятся среди «нормальных» чисел, функция СУММ проигнорирует их, и вы даже не заметите. Результат - неверные расчеты.
- Как числа выглядят как текст?
- Используем индикатор ошибок.
- Изменение формата ячейки может преобразовать текст в числа
- Специальные усилия
- Инструмент «Текст по столбцам
- Повторный вход
- Формулы для преобразования текста в числа
- Как вы можете использовать математические операции
- Удаление непечатаемых символов
- Использование макроса VBA
- Как извлечь число из текста с помощью Ultimate Suite Tool
В этом уроке вы узнаете, как преобразовать строки в «настоящие» числа.
Как определить числа, записанные как текст?
В Excel есть встроенная функция проверки ошибок, которая предупреждает вас о возможных проблемах со значениями ячеек. Он выглядит как маленький зеленый треугольник в верхнем левом углу ячейки. Если выделить ячейку с таким индикатором ошибки, появится предупреждающий знак с желтым восклицательным знаком (см скриншот ниже). Наведите указатель мыши на этот знак, и Excel предупредит вас о потенциальной проблеме: число хранится в виде текста в этой ячейке или перед ним стоит апостроф .
В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:
| Число | Строка (текстовое значение) |
• Если выбрано несколько ячеек, в строке состояния отображается «Среднее», «Число» и «Сумма» . |
• Если выбрано несколько ячеек, в строке состояния отображается только количество . • Поле числового формата показывает текстовый формат (во многих случаях, но не всегда). • В строке формулы может быть виден начальный апостроф. • Зеленый треугольник в левом верхнем углу. |
На изображении ниже вы можете видеть текстовые представления чисел справа и действительные числа слева:
Есть несколько различных способов изменить текст на числа в Excel. Мы рассмотрим их ниже, начиная с самого быстрого и простого. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет проблем, которые нельзя преодолеть. Просто нужно попробовать другие способы.
Используем индикатор ошибок.
Если в ваших ячейках отображается индикатор ошибки (зеленый треугольник в левом верхнем углу), преобразование выполняется одним щелчком мыши:
- Выберите всю область, где числа хранятся в виде текста.
- Нажмите на предупреждающий знак, а затем - Преобразовать в числа.
Таким образом, вы можете преобразовать весь столбец в числа одним махом. Просто выберите всю проблемную область, а затем нажмите восклицательный знак.
Смена формата ячейки.
Все ячейки в Excel имеют определенный формат, который сообщает программе, как их обрабатывать. Например, даже если числа записаны в ячейке таблицы, но установлен текстовый формат, они будут рассматриваться как обычный текст. С ними нельзя делать никаких расчетов. Чтобы Excel правильно интерпретировал числа, они должны быть записаны в общем или числовом формате.
Итак, первый быстрый способ изменения заключается в следующем:
- Выберите ячейки с числами в текстовом формате.
- На вкладке «Главная» в группе «Число» выберите «Общий» или «Числовой» в раскрывающемся списке «Формат» .
Или вы можете использовать контекстное меню, щелкнув по нему правой кнопкой мыши.
Последовательность действий в этом случае показана на рисунке. В любом случае вы должны использовать числовой или общий формат.
Этот способ не очень удобен, и мы унаследовали его от предыдущих версий Excel, когда не было индикатора ошибки в виде зеленого уголка.
Примечание. Этот метод не работает в некоторых случаях. Например, если вы используете текстовый формат, введите несколько чисел и измените формат на числовой. Здесь ячейка по-прежнему останется отформатированной как текст.
То же самое произойдет, если перед цифрами стоит апостроф. Это ясно указывает Excel, что написан именно текст и ничего больше.
Совет. Если зеленых уголков нет вообще, проверьте, не отключены ли они в настройках Excel (Файл - Параметры - Формулы - Числа в текстовом формате или с предшествующим апострофом).
Специальная вставка.
По сравнению с предыдущими способами этот метод требует еще несколько дополнительных шагов, но работает почти на 100%.
Выполните следующие действия, чтобы исправить числа, отформатированные как текст, с помощью специальной вставки:
- Выделите ячейки в таблице с текстовым номером и отформатируйте их в Общий, как описано выше.
- Скопируйте пустую ячейку. Для этого либо поместите в него курсор и нажмите Ctrl+C, либо щелкните правой кнопкой мыши и в контекстном меню выберите «Копировать.
- Выберите ячейки таблицы, которые вы хотите преобразовать, щелкните правой кнопкой мыши и выберите «Специальная вставка». Как вариант, можно нажать комбинацию клавиш Ctrl+Alt+V.
- В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить», а затем «Добавить» в разделе «Операция».
- Нажмите «ОК.
Если все сделано правильно, ваши значения изменят выравнивание слева направо. Excel теперь обрабатывает их как числа.
Инструмент «текст по столбцам».
Это еще один способ использования встроенных функций Excel. При использовании для других целей, таких как разделение ячеек, мастер преобразования текста в столбцы представляет собой многоэтапный процесс. Но чтобы завершить нашу метаморфозу, нажимаем кнопку «Готово» на самом первом шаге :)
- Выберите элементы (возможно, весь столбец), которые вы хотите преобразовать, и убедитесь, что их формат установлен на «Общий.
- Перейдите на вкладку «Данные», группу «Инструменты данных» и нажмите кнопку «Текст в столбцах» .
- На шаге 1 мастера развертывания выберите «Разделенные» в разделе «Формат исходных данных» и сразу нажмите «Готово» для завершения преобразования» .
Это все, что нужно!
Повторный ввод.
Если у вас не так много проблемных клеток, как мы здесь говорим, простое повторное введение их может быть хорошим вариантом.
Для этого сначала установите формат «Обычный». Затем повторно введите числа в каждом из них.
Я думаю, вы знаете, как исправить ячейку - либо двойным щелчком мыши, либо с помощью клавиши F2.
Но это, конечно, если таких "псевдономеров" немного. В остальном игра не стоит свеч. Есть много других менее трудоемких способов.
Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные функции, которые можно использовать для преобразования текста в числа в Excel. Во многих ситуациях это можно сделать быстрее с помощью формулы.
В Microsoft Excel есть специальная функция — ЗНАЧЕНИЕ. Он обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы, подлежащие преобразованию.
Функция ЗНАЧ может даже распознавать набор цифр, который содержит некоторые «лишние» символы.
Например, он распознает числа, записанные с разделителями тысяч, как пробел:
=ЗНАЧ("1000")
Преобразование числа, введенного с символом валюты и разделителем тысяч:
=ЗНАЧ("1000 $")
Обе эти формулы вернут число 1000.
Точно так же она сокращает пробелы перед числами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первой позиции и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Функция ЗНАЧ также полезна при извлечении чего-либо из строки символов с помощью одной из текстовых функций, таких как ВЛЕВО, ВПРАВО и ЦЕНТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде числа, используйте следующее:
=ЗНАЧ(ПРАВО(A2,3))
На рисунке ниже показана формула преобразования:
Если вы не нарушите функцию RIGHT с помощью VALUE, результат будет возвращен в виде набора символов, что сделает невозможным выполнение каких-либо вычислений над извлеченными значениями.
Этот способ подходит, когда вы точно знаете, сколько символов и где хотите получить, а затем превращаете их в число.
Математические операции.
Другой способ — выполнить простую арифметическую операцию, которая фактически не изменяет исходное значение. В этом случае программа по возможности сама сделает необходимое преобразование.
Что бы это могло быть? Например, сложение на ноль, умножение или деление на 1.
=А2+0
=А2*1
=А2/1
Важно, чтобы эти действия не изменили размер цифр. Выше вы видите пример таких операций: двойное умножение на минус 1, умножение на 1, сложение на 0. Наиболее изящно и просто для ввода выглядит «двойное отрицание»: ставим перед референсом два минуса, т е умножаем дважды на минус 1. Результат вычисления не меняется, и написать такую формулу очень просто.
Однако, если исходные значения отформатированы как текст, Excel также может автоматически применить правильный формат к результатам. Вы сможете определить это по контенту, выровненному по левому краю. Чтобы исправить это, обязательно установите общий формат для ячеек, используемых в формуле.
Примечание. Если вы хотите, чтобы результаты были значениями, а не формулами, используйте Специальную вставку после использования этого метода, чтобы заменить их результатами.
Удаление непечатаемых символов.
При копировании данных из других приложений в таблицу Excel с помощью буфера обмена (то есть Копировать - Вставить) часто вместе с цифрами копируется различный "мусор". Так в таблице могут появиться внешне невидимые непечатаемые символы. В результате ваши числа будут восприниматься программой как строка символов.
Эта атака может быть удалена программно с помощью формулы. Как и в предыдущем примере, в C2 вы можете написать что-то вроде этого:
=ЗНАЧ(ОТРЕЗАТЬПРОСТРАНСТВА(ПРАВИЛЬНО(A2)))
Позвольте мне объяснить, как это работает. Функция CLEAN удаляет непечатаемые символы. ТРИМ - дополнительное пространство. Функция ЗНАЧ, как мы уже говорили ранее, преобразует текст в число.
Макрос VBA.
Если вам часто нужно преобразовывать большие диапазоны данных из текстового формата в числовой, есть причина, по которой эти повторяющиеся операции создают специальный макрос, который будет использоваться при необходимости. Но для этого нужно предварительно включить макросы и панель разработчика в Excel, если это еще не сделано. Щелкните правой кнопкой мыши на ленте и установите этот раздел для отображения.
Нажмите Alt+F11 или перейдите на вкладку «Разработчик» и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Вставка - Модуль и скопируйте туда следующее небольшое выражение:
Sub Text_to_Number() Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then End Sub With Application: .ScreenUpdating = False: .EnableEvents = False = With xlManualA End I Selection.Areas rArea.Replace ",", "." rArea.FormulaLocal = rArea.FormulaLocal Следующий rArea с приложением: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End SubЗатем закройте редактор, нажав стандартную кнопку закрытия в правом верхнем углу окна.
Что делает этот макрос?
Вы можете выбрать несколько диапазонов данных для преобразования (вы можете использовать мышь, удерживая клавишу CTRL). В этом случае, если в ваших числах в качестве десятичного разделителя используется запятая, она будет автоматически заменена точкой. На самом деле в Windows чаще всего точка разделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли почему-то встречается очень часто.
Чтобы использовать этот код, выберите область электронной таблицы, которую необходимо преобразовать. Щелкните значок «Макросы», расположенный на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.
Откроется окно с доступными макросами. Находим «Текст_к_номеру», наводим на него и нажимаем кнопку «Выполнить».
Извлечь число из текстовой строки с помощью Ultimate Suite
Как вы уже видели, универсальной формулы Excel для извлечения числа из текстовой строки не существует. Если у вас есть проблемы с пониманием формул или их адаптацией для ваших наборов данных, вам может понравиться этот простой способ получить число из строки в Excel.
Дополнение Ultimate Suite предоставляет множество инструментов для работы с текстовыми значениями: убирать лишние пробелы и ненужные символы, менять прописные и строчные буквы в тексте, подсчитывать символы и слова, добавлять одинаковый текст в начало или конец всех ячеек в диапазон, преобразовать текст в числа, разбить его по отдельным ячейкам, заменить неправильные символы на правильные.
Вот как быстро получить число из буквенно-цифровой строки:
- Перейдите на вкладку «Данные Ablebits» > «Текст» и нажмите «Извлечь) :
- Выделите все ячейки с нужным текстом.
- На панели инструментов выберите кнопку выбора «извлечь числа)».
- В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите параметр «Вставить как формулу» или оставьте его неотмеченным (по умолчанию).
Я рекомендую вам включить эту функцию, если вы хотите, чтобы извлеченные числа автоматически обновлялись, как только в исходные строки вносятся изменения.
Если вы хотите, чтобы результаты не зависели от исходных строк (например, если вы планируете удалить исходные данные позже), не печатайте результат в виде формулы.
- Нажмите кнопку «Вставить результаты». Вот и все!
Как и в предыдущем примере, результаты извлечения представляют собой числа, что означает, что вы можете считать, суммировать, усреднять или выполнять любые другие вычисления с ними.
В этом примере мы решили вставить результаты в виде формул, и надстройка сделала именно то, что просили:
=ЕСЛИ(МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9},A2&"_0123456789"))>ДЛИННОЕ(A2), "",СУММПРОИЗВ(СРЕДНЕЕ(0&A2, БОЛЬШОЙ(ИНДЕКС(ДВССЫЛ(--СРЕДН(A2,СТРОКА(ДВССЫЛ("$1:$"&LIN(A2))),1))*СТРОКА(ДВССЫЛ("$1:$"&LIN(A2))),0) ,СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2))))+1,1)*10^СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2)))/10))
Трудно написать такую формулу самому, не так ли?
Если флажок «Вставить как формулу» не установлен, вы увидите число в строке формул.
Хотите попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами :)
Если вы хотите иметь этот и более 60 других полезных инструментов в Excel, воспользуйтесь этой специальной возможностью покупки, которая зарезервирована для читателей нашего блога.
Вот как преобразовать текст в числа в Excel с помощью формул и встроенных функций. Более сложные случаи, когда в ячейке есть и буквы, и цифры, мы рассмотрим в отдельной статье. Спасибо за чтение, и я надеюсь увидеть вас снова на нашем блоге!
Мы также рекомендуем:
Операторные функции СЛ. В ваших файлах Excel…
Как быстро подсчитывать слова в Excel. В этой статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛИННОЕ в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или отдельных слов в…
Как быстро извлечь число из текста в Excel. В этом кратком руководстве показано, как быстро извлечь число из различных текстовых выражений в Excel с помощью формул или специального инструмента извлечения. Вероятно, возникает проблема извлечения числа из текста…
Как умножить число на процент и сложить проценты - Мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известное нам процентное значение помогает вычислять различные числовые значения. Что такое процент от числа Как умножить число на процент Как…
Как рассчитать проценты в Excel — Примеры формул — В этом уроке вы узнаете, как быстро рассчитать проценты в Excel, найдете базовую формулу процента и еще несколько формул для расчета процентного изменения, процента от общей суммы и т д…
Округление в Excel — какую из 12 формул лучше использовать? - В этой статье объясняется, как использовать ОКРУГЛ, ОКРУГЛ, ОКРУГЛВВЕРХ, ПОЛУВНИЗ, ЦЕЛОЕ, ФИЛЬТР и многие другие функции округления в Excel. В нем также приведены примеры формул для преобразования десятичных чисел в целые или в определенные…
Функция ПРАВИЛЬНО в Excel - примеры и советы. - В недавних статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на RIGHT, который предназначен для возврата заданного количества символов с крайнего правого края…
ЛЕВАЯ функция в Excel. Примеры использования и советы. - В учебнике показано, как использовать функцию LEFT в Excel для извлечения подстроки из начала текстовой строки, извлечения текста перед определенным символом, создания формулы для возврата числа и т д. Среди…
Как извлечь текст из ячейки с помощью функции MID и специальных инструментов — MID — это одна из текстовых функций, которые Microsoft Excel предоставляет для манипулирования текстовыми строками. На самом базовом уровне он используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией DLSTR в Excel. - Ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, вероятно, попали на нужную страницу. В этом кратком руководстве вы узнаете, как использовать функцию DLSTR (LEN в английской версии
