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

Как определить числа, записанные как текст?

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

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

Число	Строка (текстовое значение)
• Стандартное выравнивание по правому краю. • Если выбрано несколько ячеек, в строке состояния отображаются «Среднее», «Количество» и «Сумма» .	• Стандартно выравнивается по левому краю. • Если выбрано несколько ячеек, в строке состояния отображается только Count . • Поле числового формата отображает текстовый формат (во многих случаях, но не всегда). • В строке формулы может быть виден открывающий апостроф. • Зеленый треугольник в левом верхнем углу.

На изображении ниже вы можете увидеть текстовые представления чисел справа и действительные числа слева:

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

Используем индикатор ошибок.

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

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

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

Смена формата ячейки.

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

Итак, первый быстрый способ изменения заключается в следующем:

1. Выделите ячейки с числами в текстовом формате.
2. На вкладке «Главная» в группе «Число» выберите «Общие» или «Числовой» в раскрывающемся списке «Формат» .

Или вы можете использовать контекстное меню, щелкнув по нему правой кнопкой мыши.

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

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

Примечание. Этот метод не работает в некоторых случаях. Например, если вы используете текстовый формат, запишите несколько чисел и измените формат на Числовой. Здесь ячейка по-прежнему останется в текстовом формате.

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

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

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

По сравнению с предыдущими методами этот метод требует некоторых дополнительных действий, но работает почти на 100%.

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

1. Выделите ячейки таблицы с текстовыми числами и установите для них формат «Общий», как описано выше.
2. Скопируйте пустую ячейку. Для этого либо поместите в него курсор и нажмите Ctrl+C, либо щелкните правой кнопкой мыши и в контекстном меню выберите «Копировать.
3. Выделите ячейки таблицы, которые хотите преобразовать, щелкните правой кнопкой мыши и выберите «Специальная вставка». Альтернативно вы можете нажать комбинацию клавиш Ctrl+Alt+V.
4. В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить», а затем «Добавить» в разделе «Операция».
5. Нажмите ОК.

Если все сделано правильно, ваши значения изменят выравнивание слева направо. Excel теперь рассматривает их как числа.

Инструмент «текст по столбцам».

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

1. Выберите позиции (или целый столбец), которые хотите преобразовать, и убедитесь, что для них установлен общий формат.
2. Перейдите на вкладку «Данные», группу «Инструменты с данными» и нажмите кнопку «Текст по столбцам» .
3. На шаге 1 мастера развертывания выберите «С разделителями» в разделе «Формат исходных данных» и сразу же нажмите «Готово», чтобы завершить преобразование» .

Это все, что нужно!

Конвертация текста в число

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

Способ 1: преобразование с помощью значка об ошибке

Самый простой и быстрый способ конвертировать текстовую версию — использовать специальный значок, сообщающий об ошибке. Этот значок выглядит как восклицательный знак, вписанный в ромбовидный значок. Он появляется, когда вы выбираете ячейки с зеленой меткой в ​​верхнем левом углу, о которой мы говорили ранее. Эта отметка еще не означает, что данные в ячейке обязательно неверны. Но цифры в текстовой ячейке вызывают в программе подозрения, что данные могли быть введены неверно. Поэтому в целях безопасности она помечает их, чтобы пользователь обратил внимание. Но, к сожалению, Excel не всегда выдает такие записи, даже если числа представлены в текстовом виде, поэтому описанный ниже метод подходит не для всех случаев.

1. Выберите ячейку, содержащую зеленый индикатор, указывающий на возможную ошибку. Нажмите на появившийся значок.
2. Откроется список действий. Выберите значение «Преобразовать в число».
3. В выбранном элементе данные сразу будут преобразованы в числовую форму.

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

1. Выделите всю область, где расположены текстовые данные. Как видите, значок один для всего диапазона, а не для каждой ячейки в отдельности. Давайте нажмем на него.
2. Откроется список, который уже известен нам. Как и в прошлый раз, выберите опцию «Преобразовать в число».

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

Способ 2: конвертация при помощи окна форматирования

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

1. Выберите область, содержащую числа в текстовой форме. Щелкните правой кнопкой мыши. В контекстном меню выберите пункт «Форматировать ячейки…».
2. Откроется окно форматирования. Как и раньше, переходим на вкладку «Номер». В группе «Числовые форматы» мы должны выбрать значения, позволяющие преобразовать текст в число. К ним относятся элементы «Общие» и «Числовые». Какой бы вариант вы ни выбрали, программа будет воспринимать числа, введенные в ячейку, как числа. Делаем выбор и нажимаем кнопку. Если вы выберете значение «Числовое», в правой части окна вы сможете настроить представление числа: ввести количество десятичных знаков после запятой, поставить разделители между цифрами. После завершения настроек нажмите кнопку «ОК».
3. Теперь, как и в случае с преобразованием числа в текст, нам нужно пролистать все ячейки, поместив курсор в каждую из них и затем нажав клавишу Enter.

После выполнения этих действий все значения в выбранном диапазоне преобразуются в нужный нам вид.

Способ 3: конвертация посредством инструментов на ленте

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

1. Выбираем выборку, которая подвергнется трансформации. Перейдите на вкладку «Главная» на ленте. Нажмите на поле выбора формата в группе «Число». Выберите пункт «Числовой» или «Общий».
2. Затем щелкните описанный нами метод более одного раза в каждой ячейке преобразованного диапазона с помощью клавиш F2 и Enter.

Значения в области будут преобразованы из текста в числа.

Читайте также: Как правильно сделать кроссворд в Ворде (Word)

Способ 4: применение формулы

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

1. В пустую ячейку, параллельную первому элементу конвертируемого диапазона, вставьте знак равенства (=) и двойной символ минус (—). Далее вводим адрес первого элемента преобразуемой области. Таким образом происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». Это означает, что в целевой ячейке мы получаем то же значение, что и изначально, но в числовой форме. Эта процедура называется двойным двоичным отрицанием.
2. Нажимаем клавишу Enter, после чего получаем готовое преобразованное значение. Чтобы применить эту формулу ко всем остальным ячейкам диапазона, мы используем дескриптор заполнения, который мы ранее использовали в функции ТЕКСТ.
3. Теперь у нас есть область, заполненная значениями с формулами. Выделите его и нажмите кнопку «Копировать» на вкладке «Главная» или используйте сочетание клавиш Ctrl+C.
4. Выберите исходную область и щелкните ее правой кнопкой мыши. В активированном контекстном списке перейдите к пунктам «Специальная вставка» и «Значения и форматы чисел».
5. Все данные вводятся в нужную нам форму. Теперь вы можете удалить транзитную область, где находится двойная двоичная отрицательная формула. Для этого выделите эту область, щелкните правой кнопкой мыши, чтобы вызвать контекстное меню, и выберите в нем пункт «Удалить содержимое».

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

Способ 5: применение специальной вставки

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

1. Введите цифру «1» в пустую ячейку листа. Затем выберите его и нажмите на ленте знакомый значок «Копировать.
2. Выделите область листа, которую необходимо трансформировать. Нажмите на него правой кнопкой мыши. В открывшемся меню дважды перейдите к пункту «Специальная вставка».
3. В окне «Специальная вставка» установите переключатель в блоке «Операция» в положение «Умножить». После этого нажмите на кнопку «ОК».
4. После этого действия все значения в выбранном диапазоне будут преобразованы в числа. Теперь при желании вы можете удалить цифру «1», которую мы использовали для конвертации.

Способ 6: использование инструмента «Текст столбцами»

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

1. Выделите фрагмент листа, содержимое которого необходимо преобразовать. Перейдите на вкладку «Данные». На панели инструментов в блоке «Работа с данными» нажмите значок «Текст по столбцам».
2. Запустится текстовый мастер. В первом окне убедитесь, что переключатель формата данных находится в положении «с разделителями». По умолчанию он должен находиться в этом положении, но было бы неплохо проверить статус. Затем нажмите на кнопку «Далее».
3. Во втором окне также оставляем все без изменений и нажимаем кнопку «Далее».
4. Но после открытия третьего окна мастера текстов нужно нажать на кнопку «Подробнее».
5. Откроется окно дополнительных настроек импорта текста. В поле «Разделитель целых и дробных чисел» введите точку, а в поле «Разделитель данных» — апостроф. Затем нажмите один раз на кнопку «ОК».
6. Возвращаемся в третье окно мастера текстов и нажимаем кнопку «Готово».
7. Как видите, после выполнения этих действий числа получили привычный для русской версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.

Повторный ввод.

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

Для этого сначала установите формат «Обычный». Затем повторно введите цифры в каждом из них.

Думаю, вы знаете, как выровнять ячейку – либо двойным щелчком мыши, либо клавишей F2.

Но это конечно если таких «псевдономеров» немного. В противном случае игра не стоит свеч. Есть много других менее трудоемких способов.

Преобразовать текст в число с помощью формулы

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

В Microsoft Excel есть специальная функция — ЗНАЧЕНИЕ. Он обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий преобразуемые символы.

Функция ЗНАЧ может даже распознавать набор чисел, содержащий некоторые «лишние» символы.

Например, он распознает числа, написанные через разделитель тысяч, как пробел:

=ЗНАЧЕНИЕ(«1000»)

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

=ЗНАЧЕНИЕ(«1000 $»)

Обе эти формулы вернут число 1000.

Точно так же он имеет дело с пробелами перед числами.

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

Функция ЗНАЧЕНИЕ также полезна при извлечении чего-либо из строки символов с помощью одной из текстовых функций, таких как LEFT, RIGHT и PST.

Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде числа, используйте следующее:

=ЗНАЧЕНИЕ(ПРАВО(A2,3))

На рисунке ниже показана формула преобразования:

Если вы не включите функцию RIGHT в VALUE, результат будет возвращен в виде набора символов, что сделает невозможными вычисления по извлеченным значениям.

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

Математические операции.

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

Что бы это могло быть? Например, сложение на ноль, умножение или деление на 1.

=А2+0

=А2*1

=А2/1

Важно, чтобы эти действия не меняли размер цифр. Выше вы видите пример таких операций: двойное умножение на минус 1, умножение на 1, сложение на 0. Самый элегантный и простой для ввода — «двойной минус»: перед ссылкой ставим два минуса, то есть мы раза два раза с минус 1. Результат расчета не изменится, и написать такую ​​формулу очень легко.

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

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

Удаление непечатаемых символов.

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

Эту неприятность можно убрать программно с помощью формулы. Как и в предыдущем примере, в C2 можно написать что-то вроде этого:

=ЗНАЧЕНИЕ(SZMELL(PECHSYMB(A2)))

Позвольте мне объяснить, как это работает. Функция PRINTCHARACTER удаляет непечатаемые символы. ПРОСТРАНСТВА – дополнительное пространство. Функция ЗНАЧЕНИЕ, как мы говорили ранее, преобразует текст в число.

Макрос VBA.

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

Нажмите Alt+F11 или откройте вкладку «Разработчик» и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Вставка — Модуль и скопируйте туда следующее небольшое выражение:

Sub Text_to_number() Dim rArea As Range При ошибке Возобновить следующий ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err, затем завершить подписку с помощью приложения: .ScreenUpdating = False: .EnableEvents = False = With xlManualAre 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.

Откроется окно с доступными макросами. Найдите «Text_to_number», наведите на него курсор и нажмите кнопку «Выполнить».