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

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

Небольшое вступление. Чего рассматривать не будем, и почему такое вообще происходит.

Сразу оговорюсь, что не буду здесь рассматривать случаи, когда в ячейках кромешный ад и вакханалия вроде "10 руб", "33 попугая" и так далее. Хотя один способ может и с такими ячейками помочь. Будем разбирать православные ячейки, в которых только число, но, по тем или иным причинам, оно сохранено как текст.

Причины могут быть разными, но чаще всего такая ситуация случается, когда мы импортируем данные в Excel из какой-нибудь другой корпоративной программы или копируем из интернета/другого источника. Выгрузил данные в Excel, пытаешься что-то просуммировать, а на выходе получаешь шиш с маслом:

Excel любезно даёт понять, что число сохранено как текст, добавляя в такие ячейки зелёный треугольник в левый верхний угол (не касается дат, сохранённых как текст, такие ячейки никак не выделяются). Интересный момент: если суммировать ячейки вручную, то результат будет нормальным.

Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная - Число - в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.

И так, что же можно сделать.

Важно! Прежде чем пробовать какой-то из способов, убедитесь, что у вас в принципе правильные разделители указаны. Если система использует запятую как разделитель групп разрядов, а у вас точки везде, нужно просто через поиск и замену поменять точку на запятую (и наоборот, если в качестве разделителя система использует точку).

Способ 1. Использовать инструмент "Текст по столбцам" (text to columns) . Выделяем все "проклятые" ячейки, идём на вкладку Данные - Работа с данными - Текст по столбцам, и на самом первом шаге нажимаем Готово.

Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят "Да какого..?! Да почему!? Да что с этой программой не так?!". Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на... Барабанная дробь... ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).

Способ 3. С помощью специальной вставки. Довольно интересный способ, так как знакомит нас со специальной вставкой - очень полезным инструментом. Смысл в том, что для преобразования текстовых чисел в нормальные числа с ними нужно произвести какое-то математическое действие. Но нужна такая операция, которая не меняет самого числа, а это, например, умножение на единицу (есть более экзотические, прибавить или вычесть ноль, но их рассматривать не будем). Алгоритм следующий:

1. В произвольную ячейку пишем единицу (1)

2. Копируем эту ячейку (Ctrl + C)

3. Выделяем диапазон с "кривыми" числами

4. По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)

5. В контекстном меню выбираем Специальная вставка (Paste special)

6. В следующем окне выбираем "Умножить". Если в таблице есть какое-то оформление, то ещё желательно выбрать "Значения", чтобы это самое оформление не слетело

7. Жмём Ок.

Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).

Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом "А что это такое тут у тебя формуле?". Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.

Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак "дорожные работы") с ошибкой, то программа сама предложит преобразовать текст в число:

То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать "Преобразовать в число", и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).

Немного другая история. Иногда, когда выгружаем данные откуда-то в Excel, в числовых ячейках попадается пробел, как разделитель групп разрядов. И в большинстве случаев все способы, про которые я писал выше, справляются с преобразованием. Но порой что-то идёт не так. И ничего не срабатывает. Отчаявшись, ты пытаешься заменить пробел на пусто, но Excel, подлец, пишет, что пробелов в ячейках не найдено. Но как, вот же он, бездушная ты слепая программа:

К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле "Найти", а заменить на пусто.

Всесильный Power Query.

Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ - это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор - кАзёл. И всё же :) Собрал все случаи, про которые писал. Дальше:

1. Желательно преобразовать таблицу в "умную" (не сделаете сами, всё равно потом Excel это сделает за вас)

2. Далее вкладка Данные - Получить и преобразовать данные - Из таблицы/диапазона

3. Если настройки PQ не меняли, то автоматически будет применён шаг "Изменённый тип", который всё и сделает

4. В PQ на вкладке Главная нажимаем Закрыть и загрузить

5. Получаем на новом листе "умную" таблицу с правильными форматами.

Итог.

На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно :) Когда какой использовать - дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.

Как всегда, всем огромное спасибо, кто потратил своё драгоценное время и внимание на прочтение данного материала. Надеюсь, было полезно и что-то из статьи поможет вам в работе.