Lumpics lumpics.ru

Преобразование числа в текст и обратно в Microsoft Excel

Одной из частых задач, с которыми сталкиваются пользователи программы Эксель, является преобразования числовых выражений в текстовый формат и обратно. Этот вопрос часто заставляет потратить на решение много времени, если юзер не знает четкого алгоритма действий. Давайте разберемся, как можно решить обе задачи различными способами.

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

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

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

  1. Выделяем те элементы листа, в которых нужно преобразовать данные в текст. Как видим, во вкладке «Главная» на панели инструментов в блоке «Число» в специальном поле отображается информация о том, что данные элементы имеют общий формат, а значит, цифры, вписанные в них, воспринимаются программой, как число.
  2. Общий формат в Microsoft Excel
  3. Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».
  4. Переход в окно форматирования в Microsoft Excel
  5. В открывшемся окне форматирования переходим во вкладку «Число», если оно было открыто в другом месте. В блоке настроек «Числовые форматы» выбираем позицию «Текстовый». Для сохранения изменений жмем на кнопку «OK» в нижней части окна.
  6. Окно форматирования в Microsoft Excel
  7. Как видим, после данных манипуляций в специальном поле высвечивается информация о том, что ячейки были преобразованы в текстовый вид.
  8. Ячейки преобоазованы в текстовый формат в Microsoft Excel
  9. Но если мы попытаемся подсчитать автосумму, то она отобразится в ячейке ниже. Это означает, что преобразование было совершено не полностью. В этом и заключается одна из фишек Excel. Программа не дает завершить преобразование данных наиболее интуитивно понятным способом.
  10. Автосумма в Microsoft Excel
  11. Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.
  12. Преобразование числа в текст в Microsoft Excel
  13. После выполнения данной процедуры со всеми ячейками области, данные в них будут восприниматься программой, как текстовые выражения, а, следовательно, и автосумма будет равна нулю. Кроме того, как видим, левый верхний угол ячеек будет окрашен в зеленый цвет. Это также является косвенным признаком того, что элементы, в которых находятся цифры, преобразованы в текстовый вариант отображения. Хотя этот признак не всегда является обязательным и в некоторых случаях такая пометка отсутствует.
Автосумма равна 0 в Microsoft Excel

Урок: Как изменить формат в Excel

Способ 2: инструменты на ленте

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

  1. Выделяем элементы, данные в которых нужно преобразовать в текстовый вид. Находясь во вкладке «Главная» кликаем по пиктограмме в виде треугольника справа от поля, в котором отображается формат. Оно расположено в блоке инструментов «Число».
  2. Переход к форматированию на ленте в Microsoft Excel
  3. В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».
  4. Выбор текстового формата в Microsoft Excel
  5. Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

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

  1. Устанавливаем курсор в первый элемент диапазона, в котором будет выводиться результат преобразования. Щелкаем по значку «Вставить функцию», который размещен около строки формул.
  2. Переход в Мастер функций в Microsoft Excel
  3. Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».
  4. Переход к аргументам функции ТЕКСТ в Microsoft Excel
  5. Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:

    =ТЕКСТ(значение;формат)

    Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».

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

    В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.

    После того, как все требуемые параметры введены, щелкаем по кнопке «OK».

  6. Аргументы функции ТЕКСТ в Microsoft Excel
  7. Как видим, значение первого элемента заданного диапазона отобразилось в ячейке, которую мы выделили ещё в первом пункте данного руководства. Для того, чтобы перенести и другие значения, нужно скопировать формулу в смежные элементы листа. Устанавливаем курсор в нижний правый угол элемента, который содержит формулу. Курсор преобразуется в маркер заполнения, имеющий вид небольшого крестика. Зажимаем левую кнопку мыши и протаскиваем по пустым ячейкам параллельно диапазону, в котором находятся исходные данные.
  8. Маркер заполнения в Microsoft Excel
  9. Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».
  10. Копирование в Microsoft Excel
  11. Далее, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимаем выделение с области, которая содержит формулы. Кликаем по ней правой кнопкой мыши. Происходит запуск контекстного списка действий. Выбираем в нем позицию «Специальная вставка». Среди вариантов действий в открывшемся списке выбираем «Значения и форматы чисел». Специальная вставка в Microsoft Excel

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

  12. Специальная вставка в исходный диапазон в Microsoft Excel
  13. В любом случае, в выбранный диапазон будут вставлены данные в текстовом виде. Если же вы все-таки выбрали вставку в исходную область, то ячейки, содержащие формулы, можно очистить. Для этого выделяем их, кликаем правой кнопкой мыши и выбираем позицию «Очистить содержимое».
Очистка содержимого в Microsoft Excel

На этом процедуру преобразования можно считать оконченной.

Урок: Мастер функций в Excel

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

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

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

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

  1. Выделяем ячейку, в которой содержится зеленый индикатор о возможной ошибке. Кликаем по появившейся пиктограмме.
  2. Значок об ошибке в Microsoft Excel
  3. Открывается список действий. Выбираем в нем значение «Преобразовать в число».
  4. Преобразование в число в Microsoft Excel
  5. В выделенном элементе данные тут же будут преобразованы в числовой вид.
Значение в ячейке преобразовано в число в Microsoft Excel

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

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

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

Преобразование в число выполнено в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

  1. В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.
  2. Формула в Microsoft Excel
  3. Жмем на клавишу Enter, после чего получаем готовое преобразованное значение. Для того, чтобы применить данную формулу для всех других ячеек диапазона, используем маркер заполнения, который ранее был применен нами для функции ТЕКСТ.
  4. Маркер заполнения для формулы двойного бинарного отрицания в Microsoft Excel
  5. Теперь мы имеем диапазон, который заполнен значениями с формулами. Выделяем его и жмем на кнопку «Копировать» во вкладке «Главная» или применяем сочетание клавиш Ctrl+C.
  6. Коопирование числовых значений в Microsoft Excel
  7. Выделяем исходную область и производим щелчок по ней правой кнопкой мыши. В активировавшемся контекстном списке переходим по пунктам «Специальная вставка» и «Значения и форматы чисел».
  8.  Применение специальной вставки в Microsoft Excel
  9. Все данные вставлены в нужном нам виде. Теперь можно удалить транзитный диапазон, в котором находится формула двойного бинарного отрицания. Для этого выделяем данную область, кликом правой кнопки мыши вызываем контекстное меню и выбираем в нем позицию «Очистить содержимое».
Очистка содержимого транзитного диапазона в Microsoft Excel

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

Урок: Как сделать автозаполнение в Excel

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

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

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

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

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

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

Способ 7: применение макросов

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

  1. Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».
  2. Переход в редактор макросов в Microsoft Excel
  3. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:


    Sub Текст_в_число()
    Selection.NumberFormat = "General"
    Selection.Value = Selection.Value
    End Sub

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

  4. Редактор макросов в Microsoft Excel
  5. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».
  6. Переход к списку макросов в Microsoft Excel
  7. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».
  8. Окно макросов в Microsoft Excel
  9. Как видим, тут же происходит преобразование текстового выражения в числовой формат.
Преобразование текста в число с помощью макросов выполнено в Microsoft Excel

Урок: Как создать макрос в Экселе

Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами». Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

16 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Коршун
2 сентября 2021 12:21

Прощелкатьвсе все ячейки — прям оптимальный способ. А если их 10000?

Коршун
2 сентября 2021 12:29

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

Анатолий
16 ноября 2017 17:35

Макрос для преобразования числа в текст в большом количестве в столбце «C» Excel — на месте
В тексте макроса нужно задать диапазон строк в параметре x (в данном случае от 30 до 7000)
Sub UpDateCell()
‘ Преобразование заполненной ячейки из числа в текст
Dim n$, x, m, l
With Application ‘ РУЧНОЙ РЕЖИМ ВЫЧИСЛЕНИЯ
.Calculation = xlManual
.MaxChange = 0.001
End With
For x = 30 To 7000
Range(«C» & x).Select
m = ActiveCell.Value
n$ = Str$(m)
l = Len(n$)
If Left$(n$, 1) = » » Then
n$ = Right$(n$, l — 1)
End If
‘ If Left$(n$, 1) = » » Then ‘дополнительно, если какое число уже было в текстовом формате
‘ n$ = Right$(n$, l — 1)
‘ End If
Selection.NumberFormat = «@» ‘текстовый
ActiveCell.Offset = n$
Next x
With Application ‘ АВТОМАТИЧЕСКИЙ РЕЖИМ ВЫЧИСЛЕНИЯ
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

Герман
21 марта 2018 09:36

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

Аноним
29 марта 2018 09:54

Спасибо!!!
Статья очень помогла.

Аноним
29 марта 2018 10:51

OBI — Ihr Baumarkt! Jetzt auch mit dem OBI Online-Shop für Sie

1169733
19 июля 2018 09:01

Добрый день,

Я получаю таблицы(прайсы) с данными из Англии, там в числах разделитель точка — 2500.35 мы пользуемся разделителем «запятой» — 2500,35.
В связи с этим вопрос: Как конвертировать числа с разделителем(.) в числа с (,), что бы можно было бы использовать формулы?

img-2018-07-19-12-00-43
Аноним
27 января 2019 10:03

Существует ли функция преобразующая целое число в текст, например:
в ячейку 1А — вводим число «5», в ячейку, например, 1В необходимо преобразовать его в текст — «Пять»?

Евгений
13 марта 2019 13:03

Здравствуйте! В ячейке в текстовом формате написано 1/2. Как в данной ячейке или в любой другой получить 0,5 или 50%?

Анна
11 января 2020 15:04

Добрый день.
Возможен формат ячейки ?: цифру 6 прописать в ячейке как «шесть»- буквами (желательно не только на русском языке)

надеюсь на ответ

Аноним
27 августа 2020 10:25

Как преобразовать число 11 в «одиннадцать», 5 в «пять» и так далее?

Роман
13 октября 2020 07:13

В Офисе 2013 макрос не работает- косяк Офиса- формат данных вроде правильный в ячейках судя по ленте (вкладка «Главное»), но формат данных в ячейках — по прежнему текстовый. Выделяешь ячейку, берешь ее на редакцию, жмешь ВВОД — вот теперь в этой ячейке формат данных соотвествует ленте.

zii
14 июля 2021 12:34

Спасибо! помогло решить мою проблему

Аноним
17 октября 2021 08:45

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

Аноним
13 июля 2022 18:41

Отличная статья. Единственный минус — формула Текст() при преобразовании Текста в Текст удаляет первый ноль.
Исходные данные в случае впереди стоящего нуля уже были текстом среди остальных чисел. Второй массив весь был текстовым. Для сводной таблицы почему-то текст, начинающийся на 7 и число, начинающееся на 7 это разные вещи и сначала шли все числа, потом эти же числа, но в виде текста. Объединялись только номера, начинающиеся на 0. Получается, формула Текст в этом случае не поможет. Пришлось с апострофом оба списка сцеплять сначала…. Копировать в блокнот и назад тоже работает. Не сразу увидела комментарии.

Задать вопрос