Перевести текст в цифры в excel. Как преобразовать числа из текстового формата в числовой

Перевод текста в число либо числа в текст при помощи функций Excel.

Зачастую при заполнении разных документов нужно указанную в числовой форме валютную сумму прописывать в текстовом формате (например, в рублях). При оформлении огромного числа документов это достаточно трудно и просит огромных трудозатрат. В «Эксель» предусмотрены несколько функций преобразования текста в число и обратно:

«ТЕКСТ», «РУБЛЬ» и «ЗНАЧЕН».

Рассмотрим раздельно каждую функцию.

Применение функции «ТЕКСТ».

  • Выбираем ячейку, в которую желаем поместить текстовое значение;
  • Вызываем мастер функций;
  • Выбираем функцию «ТЕКСТ»;
  • Указываем ячейку, из которой берем значение;
  • Обязательно указываем формат (формат можно выбрать, открыв меню «формат ячеек», вкладка – (все форматы)), можно поставить формат «Общий»;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в текстовом формате

Применение функции «РУБЛЬ».

  • Выбираем ячейку, в которую желаем поместить текстовое значение;
  • Вызываем мастер функций;
  • Выбираем функцию «РУБЛЬ»;
  • Указываем ячейку, из которой берем значение;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в текстовом формате с обозначением «руб.»

Применение функции «ЗНАЧЕН».

  • Выбираем ячейку, в которую желаем поместить значение в формате числа;
  • Вызываем мастер функций;
  • Выбираем функцию «ЗНАЧЕН»;
  • Указываем ячейку, из которой берем текстовое значение;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в числовом формате.

Часто возникает необходимость в написании чисел прописью (словами), к примеру, записать число «123», как «Сто 20 три».

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

Читайте также  Пропали контакты на виндовс фон. На мобильной Windows снова появились проблемы с контактами

Готовый макрос СУММА_ПРОПИСЬЮ можно скачать на странице: «Надстройка СУММА_ПРОПИСЬЮ»

 

Автор Master Of ExcОпубликовано Рубрики Функции

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

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

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

Оставьте комментарий