Как преобразовать в число весь столбец в excel

Как преобразовать текст в число? Текстовый формат в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Иногда значения в Excel выглядят как числа, но на самом деле это текст. Их нельзя суммировать, умножать, вычитать, хотя выглядят как числа. Дело в том, что это числа в формате текста.

Excel помогает автоматически конвертировать данные в числа при копировании из посторонних источников. Не всегда это получается. В этой статье расскажу как конвертировать такие “текстовые” числа в настоящие.

Как найти числа в текстовом формате

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

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

• Числа выровнены по левому краю
• Если выделена группа ячеек с такими “числами”, то в строке состояния не будет подсказок по сумме, среднему чисел в выделенном диапазоне
• Апостроф стоит первым символом в строке формул этой ячейки

На скриншоте ниже пример как Excel автоматически выравнивает по левому краю текстовые значения и по правому краю числовые значения.

Как конвертировать текст в числа в Excel

Конвертация текста в числа через меню ошибки

Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:

• Выделите диапазон таких ячеек
• Кликните по иконке с желтым ромбом и восклицательным знаком
• В выпадающем списке выберите пункт меню “Преобразовать в число”

Применять этот метод следует на небольшое количество ячеек. Не рекомендую использовать этот метод для конвертации сотен, тысяч строк. Excel может “зависнуть” в процессе конвертации.

Как конвертировать текст в число с помощью смены формата ячейки

Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:

• Выделите левой клавишей мыши диапазон ячеек
• На панели инструментов перейдите на вкладку “Главная”
• Перейдите в подгруппу “Число” и в выпадающем списке выберите формат “Числовой”

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

Как преобразовать текст в число с помощью специальной вставки

Этот метод требует чуть больше действий, но работает в 99% случаев.

• Выделить ячейки с числами в текстовом формате, и задать формат ячейки “Основной”
• Кликнуть левой клавишей мыши по пустой ячейке на листе
• Скопировать эту ячейку, зажав сочетание клавиш CTRL + C
• Выделить левой клавишей мыши ячейки которые хотите конвертировать в числа
• Кликнуть правой клавишей мыши и выбрать пункт “Специальная вставка” в выпадающем меню
• В диалоговом окне в категории “Вставить” поставить галочку напротив “Значения” . В разделе “Операция” ставим галочку напротив “Сложить” и нажимаем “ОК”

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

Как конвертировать текст в числа с помощью функции ЗНАЧЕН()

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

Эта функция работает со всеми форматами ячеек.

Как конвертировать текст в числа с помощью математических вычислений

Достаточно умножить значение в текстовом формате на единицу, чтобы получить значение в числовом формате в Excel.

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

Посмотрите видео урок про два способа преобразования чисел в текст в Excel:

Поделитесь в комментариях какой из методов вам кажется самым удобным в работе.

Как в «Эксель» перевести число в текст или наоборот перевести текст в числа.

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

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

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

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

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

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

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

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

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

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

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