Эксель текст функция: Пользовательский ЧИСЛОвой формат в EXCEL (Функция ТЕКСТ()). Примеры и описание

Текстовые функции (справка) — Служба поддержки Office

ASC	Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полноширинные (двухбайтовые) знаки в полуширинные (однобайтовые).
БАТТЕКСТ	Преобразует число в текст, используя денежный формат ß (БАТ).
СИМВОЛ	Возвращает символ с заданным кодом.
ПЕЧСИМВ	Удаляет из текста все непечатаемые символы.
КОДСИМВ	Возвращает числовой код первого знака в текстовой строке.
СЦЕП	Объединяет текст из нескольких диапазонов или строк, но не добавляет разделитель или аргументы IgnoreEmpty.
СЦЕПИТЬ	Объединяет несколько текстовых элементов в один.
DBCS	Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полуширинные (однобайтовые) знаки в текстовой строке в полноширинные (двухбайтовые).
РУБЛЬ	Преобразует число в текст, используя денежный формат рубля.
СОВПАД	Проверяет идентичность двух текстовых значений.
НАЙТИ, НАЙТИБ	Ищет вхождения одного текстового значения в другом (с учетом регистра).
ФИКСИРОВАННЫЙ	Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
ЛЕВСИМВ, ЛЕВБ	Возвращают крайние слева знаки текстового значения.
ДЛСТР, ДЛИНБ	Возвращают количество знаков в текстовой строке.
СТРОЧН	Преобразует все буквы текста в строчные.
ПСТР, ПСТРБ	Возвращают заданное число знаков из строки текста, начиная с указанной позиции.
ЧЗНАЧ	Преобразует текст в число независимо от языкового стандарта.
PHONETIC	Извлекает фонетические (фуригана) знаки из текстовой строки.
ПРОПНАЧ	Преобразует первую букву в каждом слове текста в прописную.
ЗАМЕНИТЬ, ЗАМЕНИТЬБ	Заменяют знаки в тексте.
ПОВТОР	Повторяет текст заданное число раз.
ПРАВСИМВ, ПРАВБ	Возвращают крайние справа знаки текстовой строки.
ПОИСК, ПОИСКБ	Ищут вхождения одного текстового значения в другом (без учета регистра).
ПОДСТАВИТЬ	Заменяет в текстовой строке старый текст новым.
Т	Преобразует аргументы в текст.
ТЕКСТ	Форматирует число и преобразует его в текст.
ОБЪЕДИНИТЬ	Объединяет текст из нескольких диапазонов или строк, вставляя между текстовыми значениями указанный разделитель. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны.
СЖПРОБЕЛЫ	Удаляет из текста пробелы.
ЮНИСИ

Текстовые функции в Excel. Часть №3

       Добрый день уважаемый читатель!

     Этой статьей я хочу закончить описание самых распространённых текстовых функций в Excel. Но спешу вас заверить что это не все доступные функции их будет побольше, но это уже тема совсем другой статьи. Как вы уже знаете, с текстом можно производить огромное количество разнообразнейших операций, которые позволят вам сделать работу эффективной и качественной. Для этих целей я и отобрал 21 текстовую функцию, чтобы разобрать по отдельности каждую из них.

    А теперь рассмотрим следующие 7 функций работы с текстовыми значениями, они будут последними в списке, но не последние по функционалу:

1.  Функция Т;
2.  Функция ПЕЧСИМВ;
3.  Функция РУБЛЬ;
4.  Функция ПОИСК;
5.  Функция ТЕКСТ;
6.  Функция СЦЕПИТЬ;
7.  Функция ФИКСИРОВАННЫЙ.

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

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

      Синтаксис функции:

            = Т(_значение_), где:

• значение – ссылка на значение которое необходимо проверить.

      Пример применения:

      Эта функции сможет удалить из вашего текста все непечатаемые знаки. Основную пользу функция принесет при использовании ее на работе с импортированными данными с других программ и содержащие символы, возможность печати которых не возможно. К примеру, это могут быть компьютерные коды низкого уровня или символы в выгруженных таблицах с другими форматами.

      Синтаксис функции:

           = ПЕЧСИМВ(_текст_), где:

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

      Пример применения:

       Рассматриваемая функция умеет превращать число в текстовое значение и прикрепляет к нему аббревиатуру денежной единицы. Кстати название функции, а также обозначение валюты зависит от настроенных языковых параметров. Преобразованное число в текст имеет денежный формат:

# ##0_р_.;-# ##0_р_. и округляется до указанного в формуле количества десятичных знаков.

       Синтаксис функции:

            = РУБЛЬ(_число_, _число_знаков_), где:

• число – это ссылка на ячейку, которая содержит число, формула или же любое число, требующее преобразований;
• число знаков – указывается сколько цифр после запятой вам нужно. Отрицательное значение делает округление слева от запятой, а положительное – справа. Если аргумент не указан, то значение по умолчанию 2 знака.

       Пример применения:       Стоить помнить, что форматирование ячейки с помощью функции РУБЛЬ, в отличие от стандартного форматирование через контекстное меню, превращает свой результат в текст, в отличие от других способов. Но, в принципе, использовать в формулах результат функции возможно, так как MS Excel имеет возможность превращать числа которые вносятся как текст, в процессе вычисления в числовые значения.

     Она очень похожа на ранее рассматриваемую функцию НАЙТИ, но отличие заключается в том у этой функции нет чувствительности к регистру символов, поэтому поиск можно сделать более гибким.

      Синтаксис функции:

           = ПОИСК(_искомый_текст_; _текст_для_начала_поиска_;[_начальная_позиция_]), где:

• искомый текст – указывается текстовое значение которое необходимо отыскать. Можно использовать символы подстановки;
• текст для начала поиска – указывается текст или ссылка на ячейку содержащую текстовое значение;
• начальная позиция – является необязательным аргументом и при его отсутствии по умолчанию имеет значение 1. Указывает с какой позиции в тексте необходимо начать поиск.

       Пример применения:

      Одна из самых простых текстовых функций в Excel с помощью которой можно форматировать числовое значения преображая его в текст используя коды форматов. Эта возможность будет полезна чтобы показать числа в необходимом виде или же произвести объединение его с любыми символами или же текстом.

     Обращаю внимание! Функция ТЕКСТ производит преображение числовых значений в текст и как следствие вычисление в дальнейшем станет невозможным. Рекомендую исходные данные сохранять отдельно от результатов использования функции. При необходимости создания формул всегда есть возможность обратится к исходным значениям, что исключит ошибку при использовании результатов работы функции ТЕКСТ.

      Синтаксис функции:

           = ТЕКСТ(_значение_;_формат_), где:

• значение – указывается числовое значение или указывается ссылка на него;
• формат – вводится формат который будет применен к аргументу «Значение».

      Пример применения:

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

        Синтаксис функции:

             = СЦЕПИТЬ(_текст№1_;_ [текст№2]_;….), где:

• текст№1 – являет собой первый элемент который нужно сцепить с другими, обязательный аргумент;
• текст№2 – не является обязательным аргументом. Предоставляет 255 значений, как элементы слияния.

       Пример применения:        Обращаю ваше внимание что функция не умеет ставить пробелы между аргументами, поэтому вы сделаете это самостоятельно. Так же при желании можно сцеплять текст с помощью знака амперсанда «&» не используя функцию. Более детально о возможностях вы прочтете в статье «Как используется функция СЦЕПИТЬ в Excel».

     Функция производит округление к указанному количеству десятичных знаков, производит форматирование в десятичном формате и возвращает полученный результат как текст.

       Синтаксис функции:

            = ФИКСИРОВАННЫЙ(_число_;_число_знаков_;_без_разделителей_), где:

• число – это ссылка на числовое значение или число, которое будет округлено и превращено в текст;
• число знаков – указываем количество цифр после запятой;
• без разделителей – этот аргумент является логическим значением и если он указан как ИСТИНА, то функция не будет включать разделители тысяч в текст который возвращается.

       Пример применения:       Обращаю ваше внимание что для аргумента «Число знаков» есть возможность указать до 127 значащих цифр, также если аргумент отрицательный, число будет округлено до десятичного знака, а в случае отсутствия аргумента, по умолчанию его значение будет равно 2. Если для аргумента «Без разделителей» указана ЛОЖЬ или он отсутствует, разделители тысяч будут включены. Также напоминаю, что отформатированное число функцией ФИКСИРОВАННЫЙ будет переделано в текст.

        Ну вот и описаны все запланированные текстовые функции в Excel. Я надеюсь подборка в 21 функцию вам понравилась и стала полезной. Не забудьте также посмотреть часть 1 и часть 2 этой статьи.

       А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

       Не забудьте поблагодарить автора!

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

ФУНКЦИИ ДЛЯ РАБОТЫ С ТЕКСТОМ В EXCEL — Трюки и приемы в Microsoft Excel

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

Функция и ее синтаксис	Краткое описание работы
СИМВОЛ(число)	Возвращает знак с заданным числовым кодом
ПЕЧСИМВ (текст)	Удаляет все непечатаемые знаки из текста. Функция ПЕЧСИМВ используется в том случае, когда текст, импортированный из другого приложения, содержит знаки, печать которых невозможна
КОДСИМВ (текст)	Возвращает числовой код первого знака во введенной текстовой строке
СЦЕПИТЬ (текст1; текст2;…)	Объединяет две или более текстовых строк в одну
РУБЛЬ (число; число_знаков)	Функция преобразует число в текстовый формат и добавляет к нему обозначение денежной единицы рубль (р.)
СОВПАД (текст1; текст2)	Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ — в противном случае. Функция учитывает регистр, но игнорирует различия в форматировании
НАЙТИ (искомый_текст; просматриваемый_текст; нач_позиция)	Функция находит вхождение одной текстовой строки в другой строке (с учетом регистра) и возвращает начальную позицию первой строки относительно крайнего левого знака второй строки
ФИКСИРОВАННЫЙ (число; число_знаков; без_разделителей)	Округляет число до заданного количества десятичных цифр, форматирует число в десятичном формате с использованием запятой и разделителей тысяч и возвращает результат в виде текста
ЛЕВСИМВ (текст; количество_знаков)	Функция ЛЕВСИМВ возвращает первые знаки текстовой строки, исходя из заданного количества знаков
ДЛСТР (текст)	Функция ДЛСТР возвращает количество знаков в текстовой строке
СТРОЧН (текст)	Преобразует знаки в текстовой строке из верхнего регистра в нижний
ПСТР (текст; начальная_позиция; число_знаков)	Функция ПСТР возвращает указанное число знаков из текстовой строки, начиная с указанной позиции
ПРОПНАЧ (текст)	Первая буква в строке текста и все первые буквы, следующие за знаками, отличными от букв, преобразуются в прописные (верхний регистр). Все прочие буквы в тексте преобразуются в строчные (нижний регистр)
ЗАМЕНИТЬ (старый_текст;нач_поз; число_знаков;новый текст)	Функция ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста
ПОВТОР (текст; число_повторений)	Повторяет текст заданное число раз. Функция ПОВТОР используется для заполнения ячейки заданным количеством вхождений текстовой строки
ПРАВСИМВ (текст; число_знаков)	Функция ПРАВСИМВ возвращает заданное число последних знаков текстовой строки
ПОИСК (искомый_текст; текст_для_поиска; нач позиция)	Функция находит одну текстовую строку внутри второй текстовой строки (без учета регистра) и возвращает номер начальной позиции первой строки, отсчитывая его от первого знака второй строки
ПОДСТАВИТЬ (текст; стар_текст; нов_текст; номер_вхождения)	Подставляет значение аргумента «нов_текст» вместо значения аргумента «стар_текст» в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке
Т (значение)	Возвращает текст, ссылка на который задается аргументом «значение»
ТЕКСТ (значение; формат)	Преобразует значение в текст в заданном числовом формате заданном
СЖПРОБЕЛЫ (текст)	Удаляет из текста все пробелы, за исключением одиночным пробелов между словами
ПРОПИСН (текст)	Делает все буквы в тексте прописным
ЗНАЧЕН (текст)	Преобразует строку текста, отображающую число, в число

Текстовые функции в Excel

Функции – это зависимости одних значений от других, возвращающие результаты выполнения определенных действий над значениями, которые выступают в качестве аргументов. Функции в Excel дают возможность упрощать формулы в ячейках листа, в случаях, когда они длинные и/или сложные.

Аргумент – это значение, которое предоставляет исходную информацию для функции.

Список текстовых функций

По любой из перечисленных функций можно получить справочную информацию с описанием, синтаксисом и примером, если в мастере функций выбрать интересующую функцию и кликнуть ссылку «Справка по этой функции».

 

Быстрое использование функций

«БАТТЕКСТ»

В справке к функции «БАТТЕКСТ» написано, что в панели управления, в разделе «Язык и региональные стандарты» можно изменить формат «бат» на другой стиль, но сделать это, как правило, никому не удается. В связи с этим, аналог этой функции на русском языке написан на встроенном в приложения Office языке программирования VisualBasicforApplications и распространяется в виде надстройки.

«ЛЕВСИМВ» и «ПРАВСИМВ»

Функции «ЛЕВСИМВ» и «ПРАВСИМВ» используются для обрезки ненужных символов в значениях ячеек, как текстовых, так и числовых. В случаях, когда необходимо чтобы урезанные значения находились в тех же самых ячейках, в которых расположен исходный текст, быстрее и удобнее производить обрезку текста минуя этап ввода формул, при помощи надстройки, которая позволяет удалять лишние символы.

«ЗНАЧЕН»

Число в Excel может быть отформатировано, как текст. С такими числами нельзя совершить никаких математических действий, они воспринимаются приложением Excel как обычный текст. Число, отформатированное как текст, можно получить, например, поставив апостроф перед числовым значением или установить в ячейке текстовый формат, а затем внести в него числовое значение. Для преобразования таких чисел в настоящие числа как раз и используется функция «ЗНАЧЕН». Кроме того, преобразовать текст в число можно без использования формулы, при помощи надстройки, которая в заданном диапазоне находит числа, отформатированные как текст и трансформирует их в настоящие числа.

«ПЕЧСИМВ», «ПРОПИСН», «ПРОПНАЧ», «СЖПРОБЕЛЫ», «СТРОЧН»

Некоторые текстовые функции, такие как «ПЕЧСИМВ», «ПРОПИСН», «ПРОПНАЧ», «СЖПРОБЕЛЫ», «СТРОЧН» часто удобно применять не в виде формул, введенных в ячейки рабочего листа, а в виде инструмента, позволяющего получить конечный результат преобразования сразу в ячейках с исходным текстом. В таких случаях удобно использовать надстройку, когда функция, осуществляющая то или иное преобразование, скрыта в программе и при этом ее не нужно вводить в ячейки листа при помощи формул.

Другие материалы по теме:

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

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

Конвертация числа в текстовый вид

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.

1. Выделяем те элементы листа, в которых нужно преобразовать данные в текст. Как видим, во вкладке «Главная» на панели инструментов в блоке «Число» в специальном поле отображается информация о том, что данные элементы имеют общий формат, а значит, цифры, вписанные в них, воспринимаются программой, как число.
2. Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».
3. В открывшемся окне форматирования переходим во вкладку «Число», если оно было открыто в другом месте. В блоке настроек «Числовые форматы» выбираем позицию «Текстовый». Для сохранения изменений жмем на кнопку «OK» в нижней части окна.
4. Как видим, после данных манипуляций в специальном поле высвечивается информация о том, что ячейки были преобразованы в текстовый вид.
5. Но если мы попытаемся подсчитать автосумму, то она отобразится в ячейке ниже. Это означает, что преобразование было совершено не полностью. В этом и заключается одна из фишек Excel. Программа не дает завершить преобразование данных наиболее интуитивно понятным способом.
6. Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.
7. После выполнения данной процедуры со всеми ячейками области, данные в них будут восприниматься программой, как текстовые выражения, а, следовательно, и автосумма будет равна нулю. Кроме того, как видим, левый верхний угол ячеек будет окрашен в зеленый цвет. Это также является косвенным признаком того, что элементы, в которых находятся цифры, преобразованы в текстовый вариант отображения. Хотя этот признак не всегда является обязательным и в некоторых случаях такая пометка отсутствует.

Урок: Как изменить формат в Excel

Способ 2: инструменты на ленте

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

1. Выделяем элементы, данные в которых нужно преобразовать в текстовый вид. Находясь во вкладке «Главная» кликаем по пиктограмме в виде треугольника справа от поля, в котором отображается формат. Оно расположено в блоке инструментов «Число».
2. В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».
3. Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ. Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.

1. Устанавливаем курсор в первый элемент диапазона, в котором будет выводиться результат преобразования. Щелкаем по значку «Вставить функцию», который размещен около строки формул.
2. Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».
3. Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:

   =ТЕКСТ(значение;формат)

   Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».

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

   В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.

   После того, как все требуемые параметры введены, щелкаем по кнопке «OK».

4. Как видим, значение первого элемента заданного диапазона отобразилось в ячейке, которую мы выделили ещё в первом пункте данного руководства. Для того, чтобы перенести и другие значения, нужно скопировать формулу в смежные элементы листа. Устанавливаем курсор в нижний правый угол элемента, который содержит формулу. Курсор преобразуется в маркер заполнения, имеющий вид небольшого крестика. Зажимаем левую кнопку мыши и протаскиваем по пустым ячейкам параллельно диапазону, в котором находятся исходные данные.
5. Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».
6. Далее, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимаем выделение с области, которая содержит формулы. Кликаем по ней правой кнопкой мыши. Происходит запуск контекстного списка действий. Выбираем в нем позицию «Специальная вставка». Среди вариантов действий в открывшемся списке выбираем «Значения и форматы чисел».

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

7. В любом случае, в выбранный диапазон будут вставлены данные в текстовом виде. Если же вы все-таки выбрали вставку в исходную область, то ячейки, содержащие формулы, можно очистить. Для этого выделяем их, кликаем правой кнопкой мыши и выбираем позицию «Очистить содержимое».

На этом процедуру преобразования можно считать оконченной.

Урок: Мастер функций в Excel

Конвертация текста в число

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

Способ 1: преобразование с помощью значка об ошибке

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

1. Выделяем ячейку, в которой содержится зеленый индикатор о возможной ошибке. Кликаем по появившейся пиктограмме.
2. Открывается список действий. Выбираем в нем значение «Преобразовать в число».
3. В выделенном элементе данные тут же будут преобразованы в числовой вид.

Если подобных текстовых значений, которые следует преобразовать, не одно, а множество, то в этом случае можно ускорить процедуру преобразования.

1. Выделяем весь диапазон, в котором находятся текстовые данные. Как видим, пиктограмма появилась одна для всей области, а не для каждой ячейки в отдельности. Щелкаем по ней.
2. Открывается уже знакомый нам список. Как и в прошлый раз, выбираем позицию «Преобразовать в число».

Все данные массива будут преобразованы в указанный вид.

Способ 2: конвертация при помощи окна форматирования

Как и для преобразования данных из числового вида в текст, в Экселе существует возможность обратного конвертирования через окно форматирования.

1. Выделяем диапазон, содержащий цифры в текстовом варианте. Кликаем правой кнопкой мыши. В контекстном меню выбираем позицию «Формат ячеек…».
2. Выполняется запуск окна форматирования. Как и в предыдущий раз, переходим во вкладку «Число». В группе «Числовые форматы» нам нужно выбрать значения, которые позволят преобразовать текст в число. К ним относится пункты «Общий» и «Числовой». Какой бы из них вы не выбрали, программа будет расценивать цифры, введенные в ячейку, как числа. Производим выбор и жмем на кнопку. Если вы выбрали значение «Числовой», то в правой части окна появится возможность отрегулировать представление числа: выставить количество десятичных знаков после запятой, установить разделителями между разрядами. После того, как настройка выполнена, жмем на кнопку «OK».
3. Теперь, как и в случае преобразования числа в текст, нам нужно прощелкать все ячейки, установив в каждую из них курсор и нажав после этого клавишу Enter.

После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.

Способ 3: конвертация посредством инструментов на ленте

Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.

1. Выделяем диапазон, который должен подвергнуться трансформации. Переходим во вкладку «Главная» на ленте. Кликаем по полю с выбором формата в группе «Число». Выбираем пункт «Числовой» или «Общий».
2. Далее прощелкиваем уже не раз описанным нами способом каждую ячейку преобразуемой области с применением клавиш F2 и Enter.

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

Способ 4: применение формулы

Также для преобразования текстовых значений в числовые можно использовать специальные формулы. Рассмотрим, как это сделать на практике.

1. В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.
2. Жмем на клавишу Enter, после чего получаем готовое преобразованное значение. Для того, чтобы применить данную формулу для всех других ячеек диапазона, используем маркер заполнения, который ранее был применен нами для функции ТЕКСТ.
3. Теперь мы имеем диапазон, который заполнен значениями с формулами. Выделяем его и жмем на кнопку «Копировать» во вкладке «Главная» или применяем сочетание клавиш Ctrl+C.
4. Выделяем исходную область и производим щелчок по ней правой кнопкой мыши. В активировавшемся контекстном списке переходим по пунктам «Специальная вставка» и «Значения и форматы чисел».
5. Все данные вставлены в нужном нам виде. Теперь можно удалить транзитный диапазон, в котором находится формула двойного бинарного отрицания. Для этого выделяем данную область, кликом правой кнопки мыши вызываем контекстное меню и выбираем в нем позицию «Очистить содержимое».

Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1». Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)

Урок: Как сделать автозаполнение в Excel

Способ 5: применение специальной вставки

Следующий способ по принципу действия очень похож на предыдущий с той лишь разницей, что для его использования не нужно создавать дополнительный столбец.

1. В любую пустую ячейку на листе вписываем цифру «1». Затем выделяем её и жмем на знакомый значок «Копировать» на ленте.
2. Выделяем область на листе, которую следует преобразовать. Кликаем по ней правой кнопкой мыши. В открывшемся меню дважды переходим по пункту «Специальная вставка».
3. В окне специальной вставки выставляем переключатель в блоке «Операция» в позицию «Умножить». Вслед за этим жмем на кнопку «OK».
4. После этого действия все значения выделенной области будут преобразованы в числовые. Теперь при желании можно удалить цифру «1», которую мы использовали в целях конвертации.

Способ 6: использование инструмента «Текст столбцами»

Ещё одним вариантом, при котором можно преобразовать текст в числовой вид, является применение инструмента «Текст столбцами». Его есть смысл использовать тогда, когда вместо запятой в качестве разделителя десятичных знаков используется точка, а в качестве разделителя разрядов вместо пробела – апостроф. Этот вариант воспринимается в англоязычном Экселе, как числовой, но в русскоязычной версии этой программы все значения, которые содержат указанные выше знаки, воспринимаются как текст. Конечно, можно перебить данные вручную, но если их много, это займет значительное количество времени, тем более что существует возможность гораздо более быстрого решения проблемы.

1. Выделяем фрагмент листа, содержимое которого нужно преобразовать. Переходим во вкладку «Данные». На ленте инструментов в блоке «Работа с данными» кликаем по значку «Текст по столбцам».
2. Запускается Мастер текстов. В первом окне обратите внимание, чтобы переключатель формата данных стоял в позиции «С разделителями». По умолчанию он должен находиться в этой позиции, но проверить состояние будет не лишним. Затем кликаем по кнопке «Далее».
3. Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».
4. А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».
5. Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».
6. Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».
7. Как видим, после выполнения данных действий числа приняли привычный для русскоязычной версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.

Способ 7: применение макросов

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

1. Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».
2. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:

   
Sub Текст_в_число()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub


   После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.

3. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».
4. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».
5. Как видим, тут же происходит преобразование текстового выражения в числовой формат.

Урок: Как создать макрос в Экселе

Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами». Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Функция ЕСЛИ СОДЕРЖИТ — EXCEL СПб

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ», чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова, или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ.

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

 

У нас есть множество строк с документами Реализации и Возвратов.

Все документы имеют свое наименование за счет уникального номера.

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

Выражение должно быть универсальным, для того, чтобы обрабатывать новые добавляемые данные.

Для того, чтобы это сделать, необходимо:

 

 

1. Начинаем с ввода функции ЕСЛИ (вводим «=», набираем наименование ЕСЛИ, выбираем его из выпадающего списка, нажимаем fx в строке формул).
   
2. В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ(), выделяем его и нажимаем 2 раза fx.

   
   

3. Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ*, добавляя в начале и в конце символ *.

   Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»

4. Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
   
5. Далее нажимаем ОК, выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

   
   

6. В Значение_если_истина вводим «Реализация», а в Значение_если_ложь – можно ввести прочерк « — »
7. Далее протягиваем формулу до конца таблицы и подключаем сводную.

   
   

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Как использовать функцию ТЕКСТ в Excel

Вы хотите преобразовать дату в название месяца? Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например «мммм» или «ммм». В показанном примере …

Функция ТЕКСТ может применять числовое форматирование к числам точно так же, как встроенные в Excel форматы ячеек для дат, валюты, дробей и т. Д.Однако, в отличие от форматирования ячеек Excel, функция ТЕКСТ работает внутри …

Этот пример состоит из двух частей для ясности: (1) формула для определения трех крупнейших сумм за каждый месяц и (2) формула для получения имени клиента для каждой из трех основных ежемесячных сумм. Обратите внимание, что нет …

Большая часть работы в этой формуле выполняется функцией ТЕКСТ, которая применяет пользовательский числовой формат для часов и минут к значению, созданному путем вычитания даты начала из даты окончания.ТЕКСТ (C5-B5, «h» …

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

Функция ТЕКСТ принимает число в качестве первого аргумента, а в качестве второго — нечто, называемое «формат_текст». Аргумент format_text работает как числовой формат и определяет форматирование, которое следует использовать, когда…

Функция ТЕКСТ применяет указанный вами числовой формат к числовому значению и возвращает результат в виде текста. В данном случае представлен числовой формат «ггггмм», который объединяет 4-значное значение года и 2-значное значение месяца ….

Примечание: значение lookup_value, равное 2, преднамеренно больше любых значений в lookup_vector, следуя концепции bignum. Работая изнутри наружу, выражение: (ТЕКСТ ($ B $ 5: $ B $ 13, «ммгг») = ТЕКСТ (E5 ,…

Функция ТЕКСТ может применять числовые форматы любого типа, включая валюту, дату, процент и т. Д. Применяя числовой формат, такой как «00», «000», «0000», вы можете «дополнить» числа любым количеством нулей. нравиться. Нули будут …

Функция ТЕКСТ принимает числовые значения и преобразует их в текстовые значения в указанном вами формате. В этом примере мы используем формат «mmm d» для обеих функций ТЕКСТ в E5.Результаты соединяются дефисом …

В этой формуле используется функция ТЕКСТ для объединения месяца и года каждой даты. Затем две даты проверяются на равенство. ТЕКСТ — полезная функция, которая позволяет преобразовывать число в текст в текстовом формате …

Примечание: значения в E5: E7 являются фактическими датами, отформатированными в пользовательском числовом формате «ммгг». Работая изнутри наружу, выражение: ПОИСКПОЗ (ИСТИНА; ТЕКСТ (дата, «ммгг») = ТЕКСТ (E5, «ммгг») использует расширение…

Эта формула строит окончательный результат из двух частей, соединенных конкатенацией с амперсандом (…

Excel поддерживает подстановочные знаки «*» и «?», И эти подстановочные знаки можно использовать для выполнения частичных (подстрок) совпадений в различных формулах поиска. Однако, если вы используете подстановочные знаки вместе с числом, вы …

По сути, эта формула извлекает число, добавляет приращение и присоединяет число к исходному тексту в правильном формате.Работая изнутри, эта формула сначала извлекает числовую часть …

Функция ТЕКСТ Excel с примерами формул

В этом руководстве объясняются особенности функции ТЕКСТ в Excel и показаны некоторые умные способы использования текстовых формул в Excel.

Microsoft Excel — это все о числах, и в ваших таблицах вы, вероятно, большую часть времени будете иметь дело с числами. Однако бывают ситуации, когда вам нужно преобразовать числа в текст, и именно здесь вступает в действие функция ТЕКСТ.

Функция ТЕКСТ в Excel — синтаксис и основы использования

По своей сути ТЕКСТ в Excel используется для преобразования числового значения в текстовую строку в определенном формате.

Синтаксис функции ТЕКСТ Excel следующий:

ТЕКСТ (значение; формат_текст)

Где:

• Значение — числовое значение, которое нужно преобразовать в текст. Это может быть число, дата, ссылка на ячейку, содержащую числовое значение, или другая функция, возвращающая число или дату.
• Format_text — формат, который вы хотите применить. Он предоставляется в виде кода формата, заключенного в кавычки, например «мм / дд / гг».

Функция ТЕКСТ доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003 и ниже.

Обычно формула ТЕКСТ Excel используется в следующих ситуациях:

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

Например, если вы хотите извлечь дату из ячейки A2 и показать ее в другой ячейке в традиционном формате даты, таком как «1 января 2016 года», используйте следующую формулу ТЕКСТА Excel:

= ТЕКСТ (A2, «мммм d, гггг»)

Результат будет примерно таким:

Важное примечание! Функция ТЕКСТ в Excel преобразует числовое значение в текстовые строки .Следовательно, вы не сможете использовать результат своей текстовой формулы в других вычислениях. Если есть такая необходимость, вы можете сохранить исходные значения (скрытые или невидимые) и использовать их в других формулах.

Коды формата функции ТЕКСТ в Excel

Как вы только что видели, синтаксис функции ТЕКСТ в Excel очень прост, сложная часть заключается в предоставлении правильного кода формата, который выведет ваш номер так, как вы хотите. По сути, функция ТЕКСТ принимает большинство кодов формата, используемых в числовых форматах Excel.В таблице ниже перечислены наиболее распространенные и часто используемые.

Код	Описание	Пример кода формата
0	Знак-заполнитель, отображающий незначащие нули.	# .00 — всегда отображает 2 десятичных знака. Если вы введете 2,5 в указанную ячейку, она будет отображаться как 2,50.
#	Цифра-заполнитель, не отображающая лишние нули.	#. ## — отображает до 2 знаков после запятой. Если вы введете 2,5, это будет отображаться как 2,5. Если вы наберете 2,555, будет отображаться как 2,56.
?	Знак-заполнитель, который оставляет место для незначительных нулей, но не отображает их. Обычно он используется для выравнивания чисел в столбце по десятичной запятой.	#. ?? — отображает максимум 2 десятичных знака и выравнивает десятичные знаки в столбце.
.(период)	Десятичная точка
, (запятая)	Разделитель тысяч.	###, ###. ## — отображает разделитель тысяч и 2 десятичных знака. Если вы наберете 250000, это будет отображаться как 250,000.00

Кроме того, вы можете включить любой из следующих символов в код формата, и они будут отображаться точно так, как было введено. Каре ‘ Апостроф {} Фигурные скобки <> Знаки «меньше» и «больше» = Знак равенства/ Прямая косая черта! Восклицательный знак и Амперсанд ~ Тильда Пробел

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

	А	B	С
1	Исходное значение	Форматированное значение	Формула
2	5,5	5,50	= ТЕКСТ (A2; «0,00»)
3		550%	= ТЕКСТ (A2; «#%»)
4		$ 5,50	= ТЕКСТ (A2; «$ #, ## 0.00 «)
5		+ 5,50 $	= ТЕКСТ (A2, «+ $ #, ## 0.00; — $ #, ## 0.00; 0.00»)
6		–5,50	= ТЕКСТ (A2; «- $ #, ## 0.00; — $ #, ## 0.00; 0.00»)
7		5 1/2	= ТЕКСТ (A2; «#? /?»)
8		5.50E + 00	= ТЕКСТ (A2; «0.00E + 00»)
9		~ 6!	= ТЕКСТ (A2, «~ #!»)

При использовании функции ТЕКСТ Excel с датами и , умноженными на , можно использовать любой из следующих кодов формата.

Код формата	Описание	Примеры
г	День месяца, или день недели	d — одно- или двузначное число без ведущего нуля (от 1 до 31) dd — двузначное число с ведущим нулем (от 01 до 31) ddd — трехбуквенное сокращение (с понедельника по воскресенье) dddd — полное название дня недели (с понедельника по воскресенье)
м	Месяц (когда используется как часть даты)	м — одно- или двузначное число без ведущего нуля (от 1 до 12) мм — двузначное число с ведущим нулем (от 01 до 12) ммм — сокращенный месяц (с января по декабрь) мммм — полное название месяца (с января по декабрь)
y	Год	гг — двузначное число (например,г. 06 означает 2006 г. или 16 означает 2016 г.) гггг — четырехзначное число (например, 2006, 2016)
ч	Час	h — одно- или двузначное число без ведущего нуля (от 1 до 24) чч — двузначное число с ведущим нулем (от 01 до 24)
м	Минуты (при использовании как часть времени)	м — одно- или двузначное число без ведущего нуля (от 1 до 60) мм — двузначное число с ведущим нулем (от 01 до 60)
с	Секунда	с — одно- или двузначное число без ведущего нуля (от 1 до 60) ss — двузначное число с ведущим нулем (от 01 до 60)
AM / PM	Время в 12-часовом формате, за которым следует «AM» или «PM»

В следующей таблице показано несколько текстовых формул Excel для дат:

	А	B	С
1	Исходная дата	Дата в формате	Формула
2	09.12.2016	09 декабря 2016	= ТЕКСТ (A2, «ммм дд гггг»)
3		Пятница, 9 декабря, 2016	= ТЕКСТ (A2, «дддд дд мммм, гггг»)
4		9 декабря 16	= ТЕКСТ (A2, «д-ммм-гг»)
5		Пятница	= ТЕКСТ (A2, «дддд»)

Как использовать функцию ТЕКСТ в Excel — примеры формул

Пока что у вас может сложиться впечатление, что использование функции ТЕКСТ в Excel весьма ограничено (поскольку отображение значения можно легко изменить с помощью диалогового окна Формат ячеек , доступного через контекстное меню ячейки или сочетание клавиш Ctrl + 1).Ситуация меняется, как только вы начинаете использовать ТЕКСТ в сочетании с другими функциями Excel. Приведенные ниже примеры дадут вам несколько вдохновляющих идей.

Объединить текст и число (или дату) в произвольном формате

При создании сводных таблиц или отчетов вам часто может потребоваться не только вычислить итоги, проценты и т.п., но и объяснить пользователям, что означает то или иное число. Для этого используйте функцию СЦЕПИТЬ, чтобы объединить текст и числа, и функцию ТЕКСТ, чтобы отобразить число (или дату), как вы хотите.

Пример 1. Форматирование чисел внутри текстовой строки

Предположим, вы рассчитываете общую сумму на основе цены единицы в ячейке A2, количества в B2 и процента скидки в C2, используя этот расчет: = A2 * B2 * (1-C2) . Чтобы пользователи точно знали, что означает выводимое число, вы хотите отобразить его вместе с пояснительным текстом, например « Ваша цена ». Кроме того, вы хотите отобразить символ валюты, разделитель тысяч и два десятичных знака.

Для этого укажите вышеуказанное вычисление в 1-м аргументе функции ТЕКСТ, включите соответствующий код формата во 2-й аргумент и объедините текстовую формулу со строкой, используя оператор амперсанда или функцию СЦЕПИТЬ:

= «Ваша цена» & ТЕКСТ (A2 * B2 * (1-C2), «$ ###, ###. 00»)

или

= СЦЕПИТЬ («Ваша цена», ТЕКСТ (A2 * B2 * (1-C2), «$ ###, ###. 00»))

Следующий снимок экрана демонстрирует результат:

Пример 2.Объедините текст и дату в желаемом формате

Когда вы возвращаете текущую дату с помощью функций СЕГОДНЯ () или СЕЙЧАС (), вы также можете захотеть отобразить ее в сочетании с некоторым текстом, чтобы никто не сомневался, к какому дню относится эта дата.

Однако, если вы попытаетесь объединить текст и дату обычным способом:

= СЦЕПИТЬ («Сегодня», СЕГОДНЯ ())

Excel вернет очень странный результат, что-то вроде « Сегодня 42198 ».

Дело в том, что во внутренней системе Excel даты хранятся в виде чисел, и это число появляется в составной текстовой строке.Чтобы исправить это, используйте функцию ТЕКСТ, чтобы отображать дату так, как вы хотите.

Например, встраивание функции СЕГОДНЯ в текстовую формулу с кодом формата «дддд д ммм, гггг» вернет строку, подобную этой: «Сегодня понедельник, 12 декабря 2016 г.».

Полная формула выглядит следующим образом:

= СЦЕПИТЬ («Сегодня», ТЕКСТ (СЕГОДНЯ (), «дддд д ммм, гггг»))
или
= «Сегодня» & ТЕКСТ (СЕГОДНЯ (), «дддд д ммм, гггг»)

Здесь можно найти еще несколько примеров формул: Объединение чисел и дат в различных форматах.

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

Как вы знаете, Microsoft Excel автоматически удаляет начальные нули, введенные перед числом в ячейке, что отлично работает в большинстве ситуаций. Но что, если вы хотите сохранить предыдущие нули?

Функция ТЕКСТ Excel может быть простым решением для дополнения чисел ведущими нулями в столбце, даже если исходные значения не одинаковой длины. Просто используйте код формата, содержащий только нулевые заполнители, например «0000000», где количество нулей соответствует количеству цифр, которые вы хотите отобразить.

Например, чтобы отобразить 7-значные числа с ведущими нулями, используйте эту формулу (где A2 — исходное число):

= ТЕКСТ (A2, «0000000»)

Как вы можете видеть на снимке экрана ниже, наша формула текста Excel добавляет столько ведущих нулей, сколько необходимо, чтобы получилась строка длиной 7 символов (помните, что результат функции ТЕКСТ в Excel всегда является текстовой строкой, даже если она выглядит как число).

Преобразование значений в телефонные номера в определенном формате

Преобразование столбца числовых значений в телефонные номера может показаться сложной задачей, но только до тех пор, пока вы не вспомните, что функция ТЕКСТ Excel позволяет использовать тире и круглые скобки в кодах формата.

Итак, чтобы отобразить число в A2 в традиционном 7-значном телефонном формате США, например, 123-456, используйте эту формулу:

= ТЕКСТ (A2, «### - ####»)

Если некоторые из исходных значений могут содержать внутренний префикс (т.е. могут быть как 7-значные, так и 10-значные числа), включите следующий код условного формата для отображения 10-значных чисел в формате (123) 456-789:

= ТЕКСТ (A2, «[<= 9999999] ### - ####; (###) ### - ####»)

На снимке экрана ниже показана эта формула текста Excel в действии:

Excel ТЕКСТ не работает - причины и решения

По сравнению с другими функциями Excel, ТЕКСТ очень прост и безболезнен, и у вас вряд ли возникнут какие-либо трудности при использовании его в электронных таблицах.Если, вопреки всем ожиданиям, текстовая формула не работает для вас, в большинстве случаев это из-за ввода неправильного кода формата в аргументе format_text . Вот две наиболее типичные проблемы:

1. Функция ТЕКСТ возвращает # ИМЯ? ошибка, если вы опустите кавычки вокруг кода формата.

   Например, формула = ТЕКСТ (A2, мм / дд / гг) неверна и должна быть записана так: = ТЕКСТ (A2, «мм / дд / гг»)

2. Функция ТЕКСТ в Excel - , зависящая от языка , и требует использования региональных кодов формата даты и времени.

Например, формула = ТЕКСТ (A2, «мм / дд / гг») , которая отлично работает для пользователей английского языка, может возвращать ошибку #VALUE в других регионах. В частности, в Германии необходимо использовать следующие коды формата: « t » вместо « d » и « j » вместо « y », потому что «день» на немецком языке означает « тег "и год -" яр "; «m» (месяц) подходит, потому что в немецком языке он также начинается с «m» (monat). Итак, в немецком Excel приведенная выше формула будет выглядеть следующим образом: = ТЕКСТ (A2; «мм / tt / jj») .Во Франции вы должны использовать код формата «mm / jj / aa», потому что «day» - это «jour», «month» - «mois», а «year» - «an».

Также обратите внимание, что в европейских странах для разделителя списков обычно используется точка с запятой, а не запятая, и поэтому ";" следует использовать для разделения аргументов формулы.

Вот как вы используете ТЕКСТ в Excel. Если кто-то хочет поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить образец рабочего листа функции ТЕКСТ Excel.

Другие текстовые функции Excel

Помимо ТЕКСТА, существует несколько других функций для работы с текстовыми значениями в Excel. Некоторые из них предназначены для преобразования текста в числа и наоборот, в то время как другие используются для управления самими текстовыми строками. Ниже вы найдете примеры формул обоих типов.

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

В этом руководстве показано, как преобразовать число в текст в Excel 2016, 2013 и 2010. Посмотрите, как выполнить задачу с помощью функции ТЕКСТ Excel и используйте число в строку, чтобы указать форматирование.Узнайте, как изменить числовой формат на текст с помощью параметров «Форматировать ячейки…» и «Текст в столбцы».

Если вы используете электронные таблицы Excel для хранения длинных и не очень длинных чисел, однажды вам может потребоваться преобразовать их в текст. Могут быть разные причины для замены цифр, хранящихся как числа, на текст. Ниже вы узнаете, почему вам может потребоваться заставить Excel видеть введенные цифры как текст, а не как числа.

• Искать по части, а не по всему номеру. Например, вам может потребоваться найти все числа, содержащие 50, например, 501, 1500, 1950 и т. Д.)
• Может потребоваться сопоставить две ячейки с помощью функции ВПР или ПОИСКПОЗ. Однако, если эти ячейки отформатированы по-разному, Excel не будет рассматривать идентичные значения как совпадающие. Например, A1 отформатирован как текст, а B1 - это число в формате 0. Начальный ноль в B2 - это настраиваемый формат. При сопоставлении этих двух ячеек Excel проигнорирует начальный 0 и не будет показывать две ячейки как идентичные. Поэтому их формат должен быть единым.
  

Та же проблема может возникнуть, если ячейки отформатированы как почтовый индекс, SSN, номер телефона, валюта и т. Д.

В этой статье я покажу вам, как преобразовать числа в текст с помощью функции ТЕКСТ Excel. Если вы не так ориентированы на формулы, взгляните на ту часть, где я объясняю, как преобразовать цифры в текстовый формат с помощью стандартного окна Excel Format Cells, добавив апостроф и используя мастер Text to Columns.

функция преобразования числа в текст Excel

Преобразование числа в текст с помощью функции ТЕКСТ Excel

Самый мощный и гибкий способ преобразования чисел в текст - использование функции ТЕКСТ.Он превращает числовое значение в текст и позволяет указать способ отображения этого значения. Это полезно, когда вам нужно показать числа в более удобочитаемом формате или если вы хотите соединить цифры с текстом или символами. Функция ТЕКСТ преобразует числовое значение в форматированный текст, поэтому результат не может быть вычислен.

Если вы знакомы с использованием формул в Excel, для вас не будет проблемой использовать функцию ТЕКСТ.

1. Добавьте вспомогательный столбец рядом со столбцом с номерами для форматирования.В моем примере это столбец D.
2. Введите формулу = ТЕКСТ (C2, «0») в ячейку D2 . В формуле C2 - это адрес первой ячейки с числами, которые нужно преобразовать.
3. Скопируйте формулу через столбец, используя маркер заполнения .
   
4. После применения формулы вы увидите изменение выравнивания влево в вспомогательном столбце.
   
5. Теперь вам нужно преобразовать формулы в значения вспомогательного столбца. Начните с выбора столбца.
6. Используйте Ctrl + C для копирования. Затем нажмите сочетание клавиш Ctrl + Alt + V, чтобы открыть диалоговое окно Специальная вставка .
7. В диалоговом окне Специальная вставка выберите радиокнопку Значения в группе Вставить .
   

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

   Теперь вы можете либо переименовать вспомогательный столбец и удалить исходный, либо скопировать результаты в свой основной и удалить временный столбец.

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

   Результат = ТЕКСТ (123,25, "0") будет 123.

   Результат = ТЕКСТ (123,25, "0,0") будет 123,3.

   Результат = ТЕКСТ (123,25; "0,00") будет 123,25.

   Чтобы сохранить только десятичные дроби, используйте = ТЕКСТ (A2, «Общие») .

   Наконечник. Допустим, вам нужно отформатировать денежную сумму, но этот формат недоступен. Например, вы не можете отображать число в британских фунтах (£), поскольку вы используете встроенное форматирование в английской версии Excel для США. Функция ТЕКСТ поможет вам преобразовать это число в фунты, если вы введете его так: = ТЕКСТ (A12, «£ #, ###, ###. ##») . Просто введите формат для использования в кавычках -> вставьте символ £, удерживая Alt и нажав 0163 на цифровой клавиатуре -> введите #, ###.## после символа £, чтобы разделить группы запятыми и использовать точку в качестве десятичной точки. Результат - текст!

Используйте параметр «Формат ячеек» для преобразования числа в текст в Excel

Если вам нужно быстро преобразовать число в строку, сделайте это с помощью опции Формат ячеек… .

1. Выберите диапазон с числовыми значениями, которые нужно отформатировать как текст.
2. Щелкните их правой кнопкой мыши и выберите в списке меню опцию Формат ячеек… .
   

   Наконечник. Вы можете отобразить окно Формат ячеек… , нажав сочетание клавиш Ctrl + 1.

3. В окне Формат ячеек выберите Текст на вкладке Число и нажмите ОК .
   

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

Добавьте апостроф, чтобы преобразовать число в текстовый формат

Если в Excel всего 2 или 3 ячейки, в которых вы хотите преобразовать числа в строку, используйте апостроф перед числом.Это мгновенно изменит числовой формат на текст.

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

Вы увидите небольшой треугольник, добавленный в углу этой ячейки. Это не лучший способ массового преобразования чисел в текст, но он самый быстрый, если вам нужно изменить всего 2 или 3 ячейки.

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

Вы можете быть удивлены, но параметр Excel Text to Columns довольно хорошо конвертирует числа в текст.Просто следуйте инструкциям ниже, чтобы увидеть, как это работает.

1. Выберите столбец, в котором вы хотите преобразовать числа в строку в Excel.
2. Перейдите на вкладку Data и щелкните значок Text to Columns .
   
3. Просто выполните шаги 1 и 2. На третьем шаге мастера убедитесь, что вы выбрали радиокнопку Text .
   
4. Нажмите Готово , чтобы ваши числа немедленно преобразовывались в текст.

Надеюсь, советы и рекомендации из этой статьи помогут вам в работе с числовыми значениями в Excel.Преобразуйте число в строку с помощью функции ТЕКСТ Excel, чтобы настроить способ отображения чисел, или используйте формат ячеек и текст в столбцы для быстрого массового преобразования. Если это всего несколько ячеек, добавьте апостроф. Не стесняйтесь оставлять свои комментарии, если у вас есть что добавить или спросить. Будьте счастливы и преуспевайте в Excel!

Вас также может заинтересовать

Текстовая функция Excel - как использовать

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

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

Рекомендуемая литература : В наших предыдущих сообщениях мы обсуждали некоторые другие строковые функции, такие как: INSTR, REPLACE, SEARCH, MATCH, SUBSTRING, MID и т. Д.

Определение и синтаксис текстовой функции:

Согласно Microsoft Excel, текстовая функция определяется как «Формула, преобразующая значение в текст в определенном числовом формате».

Синтаксис текстовой формулы следующий:

= ТЕКСТ (значение, формат_текст)

Здесь « значение » указывает число, которое вы хотите преобразовать в текст.

« формат_текста » указывает формат, в соответствии с которым должно выполняться преобразование.Обратите внимание, , , что « format_text » всегда следует заключать в кавычки. Аргумент « формат_текст » может иметь следующие значения:

Значения	Описание
0	Заставляет функцию отображать только цифры, без десятичных знаков.
#	Отображает цифру только в том случае, если она увеличивает точность числа, то есть пропускает ведущие нули и нули после десятичной точки.
.	Определяет положение десятичной точки.
0%	Форматирует полученный текст в процентах.
d	Задает день месяца или день недели, предпочтительно в однозначном представлении (например, 1, 15). Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email * Сайт