Последние несколько десятилетий на Олимпе отраслевых чартов продаж электронных таблиц восседает Microsoft Excel — интуитивно понятный по структуре софт с обширным функционалом.
При копировании текста в ячейку или скачивании файлов с просторов сети иногда возникает потребность перевести фрагмент текста в числовой формат, или не меняется формат ячейки в excel на числовой, что может вызвать сложности у некоторых пользователей. Показываем несколько рабочих алгоритмов для проведения данной операции.
На данном сайте вы найдете и другие статьи по работе с программой Excel.
Содержание
Проблема
В каждой новой рабочей книге для ячеек по умолчанию установлен числовой формат. Даже если вы помещаете в них числа, программа будет распознавать их как текст, что приводит к неправильной работе многих формул и функций, будь то сортировка или расчёт стоимости.
К сожалению, клик правой кнопкой мыши и изменение формата ячейки на числовой в выпадающем меню не разрешит проблему – внесённая ранее информация по-прежнему будет считаться текстом.
Неизвестно, была ли такова изначальная задумка разработчиков, но для завершения преобразования форматов нужно выполнить некоторые дополнительные действия.
Главный способ как преобразовать ячейку в число
Самый элементарный способ преобразования – ввести значения по-новому. Если некорректных данных у вас не слишком много имеет смысл изменить формат пустых ячеек на числовой или обычный и только потом заполнять их информацией. Введённые числа будут отображаться в нужном формате.
Для изменения формата ячеек выделите нужный вам диапазон и кликните по нему правой кнопкой мыши. В контекстном меню выберите пункт «Формат ячеек». В появившемся диалоговом окне откройте перечень форматов. Для правильной работы вам понадобится числовой или обычный. Подтвердив свои действия, закройте диалоговое окно.
Изменить формат ячеек можно и в верхней панели инструментов. Для этого на вкладке «Главная» переместитесь к блоку «Число» и в выпадающем списке кликните на нужный пункт. Вы также можете воспользоваться функцией настройки формата из выпадающего списка блока «Ячейки».
Как преобразовать в число весь столбец или всю строку
Изменить формат незаполненных ячеек целого столбца или строки нетрудно с помощью контекстного меню или инструментов верхней панели на вкладке «Главная». Выделите подлежащие форматированию ячейки и кликните по ним правой кнопкой мыши.
Выделить отдельный столбец (или преобразовать строку в число excel) можно, кликнув на его буквенное обозначение (номер), а всю таблицу – выбрав маленький треугольник на стыке лент с названиями столбцов и номерами строк. В открывшемся контекстном меню выберите пункт «Формат ячеек» и установите подходящее значение. На данном этапе вы также можете установить количество знаков после запятой, занеся информацию в соответствующее поле. Обратите внимание, что изменятся только параметры ячеек – введённые ранее данные сохранят текстовый формат.
Другие способы конвертации текста в число
Помимо, дополнительных расширений и надстроек в Excel существует также несколько дополнительных внутренних способов конвертации текстового формата в числовой: начиная от элементарных операций и заканчивая написанием отдельных макросов. В основном они предназначены для уже заполненных данными ячеек, так как после формальной замены формата введённые ранее значения не изменяются, а значит, и применяемые к ним формулы могут работать некорректно.
Преобразование с помощью уведомления об ошибке
При заполнении текстовой ячейки численным значением, за исключением различных дат, Microsoft Excel выдаёт небольшое уведомление об ошибке. На практике это выглядит как небольшой светло-зелёный флажок в левом верхнем углу ячейки и знак предупреждения. Кликнув правой кнопкой мыши на иконку уведомления, можно выбрать пункт «Преобразовать текст в число» в контекстном меню. Теперь значения будут распознаны как «численные».
Если программа никак не обозначает возможную ошибку, стоит проверить настройки. Зайдите во вкладку «Файл» и в боковом меню кликните на пункт «Параметры». Во всплывшем диалоговом окне выберите вид параметров «Формулы» подпункт «Проверка ошибок» и поставьте галочку напротив надписи «Включить фоновую проверку ошибок». В этом же окне вы можете изменить цвет появляющихся уведомлений. Нажмите «ОК» и закройте все
лишние окна.
Отличить численное значение от сохранного в текстовом формате, даже если уведомление об ошибке отсутствует, достаточно просто: обычно «правильные данные» находятся в ячейке справа, а текстовые строки начинаются слева.
Окно форматирования
Самый простой вариант для преобразований подобного рода – изменить формат для каждой ячейки в отдельности. Если некорректных значений у вас немного, то достаточно лишь выполнить формальную замену в контекстном меню на числовой формат в excel. И хотя всё указывает на логическое завершение операции, не спешите расслабляться.
Для окончательной смены формата выберите проблемную ячейку двойным щелчком мыши и лёгким движением руки нажмите Enter, чтобы создать область числового формата. Несколько ускорить процесс может использование кнопки F2 вместо выделения мышью. Проделайте эту операцию с каждой текстов ячейкой, нуждающейся в замене. Теперь вы можете смело вводить текст и не бояться сообщений об ошибке.
Инструменты в ленте
Для того чтобы провести преобразование текста в число в excel, можно воспользоваться инструментами верхней панели. Этот метод можно назвать разновидностью предыдущего. Заранее выделив ячейки, перемещаемся на вкладку «Главная» и раскрываем выпадающий список чисел, чтобы выбрать необходимый формат ячеек. Далее выделяем первую ячейку и зажимаем комбинацию F2 + Enter. Повторите эту последовательность действий для каждой следующей ячейки, числа внутри будут преобразованы в соответствующий формат.
Если в вашей рабочей книге много значений, из которых только небольшая часть сохранена в некорректном формате, можно воспользоваться инструментом поиска ошибок. Для этого откройте вкладку «Формулы» в верхнем рабочем меню и в блоке «Зависимость формул» кликните на маленький жёлтый предупреждающий знак с синей галочкой.
В открывшемся диалоговом окне будет выделена ячейка с ошибкой, её характеристика, в данном случае «Число сохранено как текст», и способ исправления. При нажатии кнопки «Преобразовать в число» программа выполняет преобразование и автоматически переходит к следующей обнаруженной ошибке.
Если число, нуждающееся в преобразовании формата, имеет дробную часть или же является датой, можно воспользоваться функцией замены. Перейдите во вкладку «Главная» и в блоке «Редактирование» выберите пункт «Найти». Осуществив поиск разделяющих знаков и убедившись в их корректности, замените их на идентичные. Обратите внимание, что данный метод имеет место только в том случае, если в ячейке, помимо числа, нет посторонних символов.
В Excel допустимыми разделителями для числовых значений и дат является косая черта с наклоном вправо и тире. Любые другие символы-разделители будут препятствовать нормальной работе программы, в том числе не позволят изменить текстовый формат в числовой. Прежде, чем вставлять номера ячеек с подобным содержимым в формулы конвертации имеет смысл провести подготовительную работу. Выделите таблицу и воспользуйтесь функциями поиска и замены, чтобы сделать возможным обработку данных функциями ЗНАЧЕН() и ДАТАЗНАЧ(), подробнее о которых вы можете прочитать в разделе «Использование функций».
Вы также можете ограничить формат для некоторых ячеек, чтобы ввести числа в формате текста было попросту невозможно. Таким образом, даже если у вас отключена функция автоматического поиска возможных ошибок, вы не сможете ввести данные в некорректном формате – программа предупредит вас об ошибке. Для этого перейдите во вкладку «Данные», блок «Работа с данными» и кликните на подпункт «Проверка данных». В открывшемся диалоговом окне выберите допустимый формат и диапазон для вводимых значений. Подтвердите ваши действия и закройте диалоговые окна.
Применение формулы
В excel преобразование текста в число можно использовать несколько формульных методов, в зависимости от причины, по которой программа присвоила данным текстовый формат.
Если числа разделяют неопознанные пробелы, которые, помимо прочего, не удаётся удалить или заменить, имеет смысл использовать функцию СЖПРОБЕЛЫ(), в противном случае Excel упорно будет оставлять данные в текстовом формате.
СЖПРОБЕЛЫ() — функция, убирает пробелы в тексте выбранной ячейки.
=СЖПРОБЕЛЫ(ячейка).
Когда пробелы стоят в начале или конце данных, их возможно удалить вручную, но объём данных слишком велик, может пригодиться функция TRIM(), сокращающая лишние отступы в начале и конце текста.
=TRIM(текст), где “текст” — ячейка или текстовая строка, которую нужно очистить от пробелов.
При копировании текста в ячейку могут быть скопированные невидимые непечатные символы, которые не позволят изменить формат данных на нужный. Для их устранения подойдёт функция ПЕЧСИМВ().
=ПЕЧСИМВ(текст)
Текст — данные на листе, ячейка, из которых требуются удалить непечатаемые знаки.
Пример: Текст = 612 867 878 789 Получается результат: 612867878789.
Чтобы извлечь в Excel число из ячейки с текстом, существует функция ЗНАЧЕН().
=ЗНАЧЕН(текст)
Где текст представлен в виде текстовой строки, в кавычках, или адресом ячейки, в которой содержится текст.
Она способна стирать пробелы и преобразовывать текст в необходимы формат. Дополнить её могут функции ПРАВСИМВ(), ЛЕВСИМВ() и ПСТР(), указывающие сколько символов и с какой стороны должна распознать программа. Обратите внимание, при форматировании времени произойдёт отделение секунд (например, 11:45 превратится в 11:45:00).
Описание функций:
=ПРАВСИМВ(текст; число_знаков)
Функция имеет два аргумента:
«Текст» — либо текстовое выражение или адрес элемента листа.
«Число знаков» — количество символов в текстовом выражении, отсчитывая справа, нужно вывести в итоговой ячейке.
=ЛЕВСИМВ(текст; число_знаков)
Аналогично предыдущей функции, только отсчитывать слева.
=ПСТР(ячейка; начальный символ; количество символов)
Начальный символ имеется в виду, с какого символа будет выбираться значение
В противном случае используется математическая операция. Этот метод также иногда называют методом бинарного отрицания. Подходит в случае, если рядом у вас есть свободный столбец или строка, куда можно переместить значение в нужном формате. Microsoft Excel автоматически конвертирует числа, с которыми выполняются математические операции, в подходящий вид. Используют операции, которые не изменяют вводное значение, например, умножение на единицу. В этом заключается метод бинарного отрицания: значение в ячейки дважды подряд умножают на минус единицу. Двойное отрицание даёт положительный результат, и содержимое выражения не изменяется. Записывается это как два минуса перед адресом.
=—АВ
Excel формулу текста в число можно растянуть на нужный вам диапазон значений. Операция также эффективно для числовых значений даты и времени, сохранённых как текст.
Опция превращения текста в число полезна и в том случае, когда нужно оставить комментарий к ячейке или содержащейся в ней формуле. Для этого после основных данных ячейки пропишите плюс и функцию Ч() с помещённым внутрь скобок комментарием. Обратите внимание, что текст должен быть заключён в кавычки. Оператор Ч() не влияет на результаты вычислений и отображается только в поле ввода при выборе ячейки.
Выполнить извлечение числовых данных из длинных текстовых строк, где среди слов затерялось искомое значение, можно в два этапа через функции поиска и оператора ПРАВСИМВ() с использованием дополнительных ячеек для промежуточного и конечного результата. В «черновой» ячейке прописываем формулу для вычисления положения первого символа — числа.
=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9}; ЯЧЕЙКА& «0123456789»))
Получив численное значение, переходим к полю вывода и заполняем её комбинацией.
=ЗНАЧЕН(ПРАВСИМ(ячейка; ДЛСТР(ячейка – черновая ячейка + 1))
Конечно, слова «ячейка» и «черновая ячейка» заменяются на соответствующие адреса (например, С7 и D7). Комбинирование с оператором ЗНАЧЕН() выполняется для сохранения итоговой информации в числовом формате, т.к. ПРАВСИМВ() сам по себе возвращает только текстовые значения.
Если текстовое значение, заключенное в ячейку, является датой – Excel не отметит её как ошибку. Например, «8 ЯНВАРЯ 2024», для программы не является полноценной датой и производить вычисления с ней не получится. Чтобы некорректную дату в числовой формат, можно использовать функцию ДАТАЗНАЧ(). После применения оператора дата примет вид 08.01.2024 и будет распознаваться как «число». Стоит отметить, что ДАТАЗНАЧ() имеет ряд ограничений: невозможно выполнить преобразование даты с неподходящими разделителями или вовсе не имеющей их. Кроме того, невозможно форматирование дат, написанных на любом из иностранных языков.
Если вам встретилась дата, не попадающая в область определения оператора преобразования текстовых дат в число, вы можете применить небольшую надстройку ПОДСТАВИТЬ(), чтобы не редактировать данные отдельно от формулы. Если ввод даты выполнен с использованием запятой вместо точки или косой черты, необходимо сначала заменить запятую на точку с помощью функции ПОДСТАВИТЬ, а затем использовать функцию ДАТАЗНАЧ для преобразования полученного текста в действительную дату.
Также для преобразования даты из текстового формата в числовой можно воспользоваться формулой.
=ТЕКСТ(Excel_Дата;»ДД.ММ.ГГГГ»;)
К сожалению, данная формула может быть применима только к русскоязычному формату даты из-за внутренней структуры «ДД.МММ.ГГГГ».
Опция специальной вставки
В отличие от предыдущего способа, специальная вставка позволяет изменить значения без перемены положения. Замените формат ячеек через контекстное меню на числовой. В любой свободной ячейке запишите цифру один, скопируйте её в буфер обмена и вызовите диалоговое окно специальной вставки. Сделать это можно комбинацией клавиш Ctrl + Alt + V или через правую кнопку мыши, выбрав одноименный пункт. В разделе «Вставка» выбираем подпункт «Значения», а в разделе операция – умножение. Стоит отметить, что можно использовать не только единицу, но и любые числа и операции, не изменяющие исходное значение (к примеру, вычитание нуля или деление на единицу).
Функция специальной вставки сработает в качестве преобразователя и в том случае, если вы прибавите копию пустой ячейки, однако этот метод подходит не для всех видов числовых значений.
Инструмент текст столбцами
Первоначально данная функция использовалась для разделения слипшегося текста. Чтобы превратить с её помощью текст в число, выделите ячейки с некорректным форматом данных и откройте в верхнем меню функцию «Разделение текста по столбцам» во вкладке «Данные». Обратите внимание, что таким способом за раз можно форматировать только один столбец со сколь угодным количеством ячеек. Преобразовать в число весь столбец в excel получится очень просто.
В диалоговом окне «Мастера распределения по столбцам» дважды нажмите на кнопку «Далее» и выберите на последней странице в блоке «Формат данный столбца» пункт «Общий». В поле данных вы можете указать диапазон ячеек для перемещения отформатированных данных или же перезаписать имеющиеся. На этом этапе вы также можете в пункте «Дополнительные настройки импорта текста» настроить разделитель дробной части чисел. Подтвердите изменение параметров и закройте все диалоговые окна.
Применение макросов
Если необходимость преобразовать текст в число эксель возникает всё чаще, можно создать отдельный алгоритм в библиотеке макросов, чтобы изменять формат ячеек по одному нажатию в любой рабочей книге. Для этого зайдите в раздел инструментов разработчика и выберите пункт «Visual basic» (VBA). Далее нажмите на пункт «Insert – Module» и введите новый макрос.
Если вкладка инструментов разработчика не отображается, вы можете попасть напрямую в книгу макросов через вкладку «Вид» и выпадающий список рядом с командой «Макросы». Выберите пункт «Запись макроса» и в открывшемся диалоговом окне в соответствующие поля введите код и сочетание клавиш для его активации. Сохранив изменения, закройте лишние окна.
Если необходимо изменить записанный ранее макрос, выберите в упомянутом ранее выпадающем списке пункт «Макросы». По клику клавиши «Изменить» программа отправит вас в окно разработчика для детального изучения введённых значений.
Код может выглядеть так:
******************************************
Sub Convert_Text_to_Numbers()
Selection.NumberFormat = «General»
Selection.Value = Selection.Value
End Sub
******************************************
Или так (для одного столбца переформатирование до первой пустой ячейки):
******************************************
Sub m()
Dim i&, x As Double i = 1 Do If Len(Cells(i, 1)) = 0 Then Exit Do x = Cells(i, 1) + 1 Cells(i, 1) = x — 1 i = i + 1 Loop
End Sub
******************************************
Или же так:
******************************************
Sub Текст_в_число()
Dim rArea As Range
On Error Resume Next
ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
If Err Then Exit Sub
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
For Each rArea In Selection.Areas
rArea.Replace «,», «.»
rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
******************************************
И даже так (Данный пример базируется на замене разделительных знаков на системные разделители):
******************************************
200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub www()
Cells.Replace «.», «.», xlPart
Cells.Replace «,», «.», xlPart
End Sub
******************************************
Один из самых популярных макросов наравне с примером 1, не забудьте перед запуском указать необходимый диапазон:
******************************************
200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Repair_Value()’ в выделенных ячейках исправить экспортированные как текст данные, чтобы нормально обознались числа
Dim rArea As Range
On Error Resume Next
ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
If Err Then Exit Sub
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
For Each rArea In Selection.Areas
rArea.FormulaLocal = rArea.FormulaLocal
Next rArea
With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub
******************************************
А данный код осуществляет перевод выделенной области в числа и удаляет неразрывные пробелы. Его можно применять для преобразования текстовых чисел, полученных после сканирования и работы с программой 1С.
******************************************
Sub Текст_в_числа()
Dim temp As String, Chislo As Double
Set r = Application.Selection With r .Replace What:=» «, Replacement:=»», LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
temp = CStr(.Cells(i, j).Value)
temp = Replace(temp, Chr(160), «», 1, , vbBinaryCompare)
temp = Replace(temp, «,», «.», 1, , vbTextCompare)
Chislo = Val(temp) .Cells(i, j).Value = Chislo
Next
Next
End With
End Sub
******************************************
Вместо строки 3 можете указать конкретный лист и диапазон:
Set r = Sheets(«Лист1»).Range(«A1:E100»)
Представленные выше коды специализируются на конвертации числовых данных, которые по какой-либо причине оказались сохранены как текст, в подходящий формат. Но если вам необходимо из строки с длинным фрагментом текста, извлечь численный фрагмент и экспортировать в отдельную ячейку в соответствующем расширении, пригодится функция
= ЧИСЛОИЗСТРОКИ(ТЕКСТ).
При чём тут функция, когда речь идёт о макросах? Дело в том, что данной опции в стандартном наборе инструментов Microsoft Excel нет — изначально она входила в состав одного из многочисленных расширений. Добавить её можно, создав следующий макрос:
******************************************
Function ЧИСЛОИЗСТРОКИ(СТРОКА As String)
Dim sSymbol As String, sWord As String
Dim i As Integer
If СТРОКА = «» Then ЧИСЛОИЗСТРОКИ = «Н/Д»: Exit Function
sWord = «»: sSymbol = «»
‘ Проходим по каждому символу
For i = 1 To Len(СТРОКА)
sSymbol = Mid(СТРОКА, i, 1)
If LCase(sSymbol) Like «*[0-9.,;:-]*» Then
If LCase(sSymbol) Like «*[.,]*» And i > 1 Then
If Not Mid(СТРОКА, i — 1, 1) Like «*[0-9]*» Or Not Mid(СТРОКА, i + 1, 1) Like «*[0-9]*» Then
sSymbol = «»
End If
End If
sWord = sWord & sSymbol
End If
Next
ЧИСЛОИЗСТРОКИ = sWord
End Function
******************************************
Функция выбирает из текстовой строки численные символы и выводит его в уже в пригодном для вычислений числовом формате. Например, из ячейки «Технониколь РОКЛАЙТ минеральная вата утеплитель плита Листвяги арт.6484930» на выходе получаем 6484930, а из «Тип – самый крупный тайфун в истории, достигавший на пике развития протяженности 2200 км» число 2200 без единиц измерения.
Сохраните написанный вами код. Для выполнения алгоритма выберите подлежащие изменению ячейки, кликните на пункт макросы и выберите из библиотеки нужный. Вы также можете воспользоваться комбинацией Alt + F8 для вызова библиотеки макросов.
Прикладываем видео по данной теме:
Преобразовать текст в число достаточно просто, если знать подходящий вашему случаю алгоритм. Независимо от того, нужно ли изменить столбец или разбросанные по рабочей книге ячейки – вы без труда сможете сделать это при помощи прямых и косвенных функций программы. Выбор же конкретной функции во многом зависит от характера данных причины считывания числовых значений как текстовых. Определённо можно сказать только одно – теперь вы сможете выполнить преобразование в любой рабочей книге Microsoft Excel.