Как преобразовать число в текст в Excel?
На нашем занятии мы подробно рассмотрим наглядные примеры преобразования числа в текст. Представим такую ситуацию. Мы имеем прайс-лист, полученный из 1С или скачанный из Internet. Необходимо по коду определить розничную цену. Мы используем функцию ВПР, но, к удивлению, видим, что этого товара якобы нет, хотя код явно присутствует в списке.
Рисунок 1
Что же произошло? Ошибся Excel? Вовсе нет. Просто для программы важное значение имеет формат значения, которое обрабатывается. Мы указали число, а в прайс-листе код представлен в виде текста. Новички в таких случаях сразу постараются предварительно задать для ячейки с искомым кодом текстовый формат. Однако если значение уже было введено, то изменение формата ячейки невыход. Excel различает формат ячейки и формат значение в нем. Формат ячейки определяет ее внешний вид – цвет, границы, а так же формат БУДУЩЕГО значения в ней. Будущего, но не текущего! А формат ячейки определяет операции, которые данное значение поддерживает. Кроме этого, полученную цену возможно потребуется сразу использовать для других расчетов, например для вычисления общей суммы по заказанным товарам. В этом случае формат ячейки значения точно не имеет, так как мы сразу будем указывать коды числами, а в прайс-листе они, как мы убедились, в текстовом формате.
Для решения этой задачи есть по крайней мере три способа. Начнем с самого простого. Используем функции Excel, результатом которых будет текстовая строка. К примеру, возьмем функцию ЛЕВСИМВ и ПРАВСИМВ. Они извлекают из заданной строки нужное количество знаков слева или справа соответственно. При этом не имеет значение, является ли первоначальное значение текстом или числом. Поэтому если указать количество знаков, которые надо извлечь равным общему количеству знаков в значении, то мы и получим ту же строчку, но уже в виде именно текста. Узнать же количество знаков в строке можно функцией ДЛСТР. В итоге получаем следующий вариант:
Рисунок 2
Обратите внимание, что проверка с помощью функции ЕТЕКСТ показала, что наше значение действительно стало текстом.
Применим этот способ к ситуации с прайс-листом, указанной выше. Как видим, все сработало!
Рисунок 3
Кроме этого, можно использовать и одну хитрость. Дело в том, что в числовом значении не может быть больше 15 значений вместе с запятой! Поэтому если указать в данных функциях заведомо большое количество знаков, например 20, то Excel так же выведет все знаки числа, преобразовав их в текст!
Получаем формулу:
=ПРАВСИМВ(A1;20)
Рисунок 4
Для прайс-листа составная функция будет такой
=ВПР(ПРАВСИМВ(B1;16);$D$4:$J$11618;3;0)
Рисунок 5
Еще раз обращаю внимание, что в данном случае можно применить как ПРАВСИМВ, так и ЛЕВСИМВ, а количество знаков можно указать любое, но больше 15.
В качестве третьего варианта используем функцию текст. Она преобразовывает значение в текст, предварительно задав нужный формат. Используем знак @ для указания именно текстового формата.
Получаем формулы
=ТЕКСТ(A1;”@”)
Рисунок 6
=ВПР(ТЕКСТ(B1;”@”);$D$4:$J$11618;3;0)
Рисунок 7
Наконец, можно попросту использовать функцию СЦЕПИТЬ. Изначально она предназначена для объединения разных наборов символов в виде строк, причем неважно какого формата, в единую текстовую строку. Но кто сказал, что использование именно нескольких исходных строк – это обязательное условие ? Поэтому почему бы не преобразовать число в текст так, как это показано ниже? Применим формулы
=СЦЕПИТЬ(A1)
Рисунок 8
=ВПР(СЦЕПИТЬ(B1);$D$4:$J$11618;3;0)
Рисунок 9
Как видно из примеров, преобразовать число в текст не так уж и сложно. Если же вам надо не просто преобразовать число в текст, сохранив внешний вид, но и предварительно поменять его, например, добавив или убрав разделители разрядов, или округлив до нужного разряда после запятой, тогда данные варианты надо усложнить. Вам необходимо применить более сложные форматы для функции ТЕКСТ или специально предназначенную функцию ФИКСИРОВАННЫЙ. Подробнее вы можете ознакомиться с ними в нашем видеоуроке.
Если же вам необходимо преобразовать не просто число в текст, а сделать его прописью, например, для платежных документов, то рекомендую прочитать статью, которая рассказывает наглядно об этом нюансе.
Текущий же урок завершен, всем желаю удачи и солнечных дней. Встретимся на занятиях!
Если у Вас остались вопросы или есть предложения, пишите об этом в комментариях или свяжитесь с нами, используя нашу контактную информацию.
