Как уже говорилось ранее, значения ячеек в текстовом формате отображаются точно так же, как вводятся. Excel воспринимает эти значения как строки, то есть как текстовые значения, независимо от того, содержатся ли в ячейках текст или числа. Другими словами значения 1 и «1» Excel понимают по-разному. Получается, что число можно отобразить и как число, используя числовой формат, и как текст, используя формат текстовый. Разница между этими числами заключается в том, что числа в числовом формате участвуют в расчетах, а числа в текстовом формате – нет! Это может приводить к ошибкам в вычислениях. Как отличить эти числа друг от друга?

Как отличить настоящее число от числа, отформатированного как текст?

Разработчики Excel об этом позаботились. Числа в ячейках с числовым форматом выравниваются по правому краю ячеек, а с текстовым форматом – по левому. Кроме того, Excel осуществляет фоновый контроль ошибок, конечно при условии того, что он настроен в параметрах Excel, в разделе «Формулы».

контроль ошибок в Excel

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

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

1) Если в ячейку с текстовым форматом, внести числовое значение, а затем формат ячейки изменить на числовой, то число остается отформатированным как текст и не будет участвовать в расчетах;

2) Если в настройках Excel отключен контроль ошибок, то индикатор ошибок не отображается, при этом числа отформатированные как текст, становятся незаметными, что может привести к ошибкам в вычислениях;

3) Если контроль ошибок включен, а правило контроля «Числа, отформатированные как текст или с предшествующим апострофом» выключено, то индикатор ошибок не отображается и числа, отформатированные как текст, становятся незаметными, что также может привести к ошибкам в расчетах;

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

Внимательный читатель может спросить «А как же выравнивание числовых значений по правому краю, а текстовых – по левому? Видно же различие!» Видно, если не установлен автоподбор ширины столбцов, если ширина столбцов заметно больше длины значений, находящихся в ячейках, и выравнивание по разным краям ячейки визуально заметно, если не установлено выравнивание по центру. Кроме всех перечисленных «если» числа с текстовым форматом могут попадать в таблицы при переносе этих таблиц в Excel, например, из текстового редактора Word.

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

Как преобразовать текст (строку) в число?

Для того, чтобы преобразовать числа, отформатированные как текст, в настоящие числа, необходимо выделить ячейку (переместить в нее рамку табличного маркера), после этого кликнуть левой кнопкой мыши на значке информационного сообщения индикатора ошибок и выбрать пункт "Преобразовать в число".

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

Можно также в выбранной ячейке нажать клавишу F2 на клавиатуре и затем нажать клавишу Enter. Помогает, но не всегда! Можно изменить формат ячеек на числовой формат и заново ввести значения вручную. Для исправления нескольких ячеек тоже вариант. В некоторых случаях можно воспользоваться умножением на единицу или поиском/заменой непечатаемых символов.

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

Надстройка для поиска и преобразования чисел, отформатированных как текст в настоящие числа

Надстройка представляет собой xla-файл, в котором сохранен программный код, написанный на встроенном в приложения Office языке программирования. Надстройка устанавливается через меню приложения Excel (установка надстроек) и по завершении установки в Excel появляется новая панель инструментов либо новая кнопка на вкладке "Надстройки".

makros-preobrazovaniya-teksta-v-chisla

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

kak-preobrazovat-tekst-v-chislo-Excel

smotret-videoвидео по преобразованию строк в числа

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

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

Надстройка позволяет удалять апострофы перед числами, находить числа, отформатированные как текст и преобразовывать их в реальные числа, а также удалять начальные, конечные пробелы и другие непечатаемые символы (Tab, Alt+Enter) в выбранном диапазоне значений.