Все знают, что табличный процессор Microsoft Excel предназначен для обработки числовой информации. Его функционал позволяет обрабатывать большие объемы вычисляемых данных. А ведь с его помощью можно обработать и текст. Это могут быть фамилии, имена, отчества сотрудников, почтовые адреса и много другой текстовой информации, записанной в ячейки таблиц.
Различные сервисы интернета позволяют осуществлять импорт данных в формате электронных таблиц и не всегда эти данные удобны для дальнейшей обработки. А если требуется скорректировать текст на сотнях строк , то без текстовых функций не обойтись. В этой статье я предлагаю вам ознакомиться с текстовыми функциями в Excel и разобраться на примерах с их использованием.
Текстовые функции Microsoft Excel
Сначала рассмотрим основные текстовые функции, которые использованы в примерах ниже. Excel использует русские названия функций в отличие от своего ближайшего аналога LibreOffice.Calc. Ниже приведен формат записи текстовой функции и ее действие.
ЛЕВСИМВ(текст;[число_знаков]) — выводит первый символ или заданное число первых символов из текстовой строки.
НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]) — находит позицию начала вхождения искомого текста в заданной текстовой строке с учетом регистра.
ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция]) — тоже самое, как и Найти, но без учета регистра.
ПСТР(текст;начальная_позиция;число_знаков) — выводит заданное число символов из строки текста, начиная с указанной позиции.
ДЛСТР(текст) — считает количество знаков в текстовой строке.
Задача 1. Объединение текстовых строк
Есть список сотрудников. Фамилии, имена и отчества разнесены в отдельные столбцы. Необходимо объединить данные в один столбец.
Решение. Задача достаточно простая и для ее реализации воспользуемся функцией СЦЕПИТЬ.
В ячейку D1 запишем формулу =СЦЕПИТЬ(A1;» «;B1;» «;C1). Можно воспользоваться мастером функций.
Далее скопируем ее на весь необходимый диапазон столбца D.
Если в функции указать только адреса ячеек, то текст склеится в одну длинную строку. Поэтому добавляем пробел в кавычках между адресами ячеек.
Посмотрите на рисунок ниже. Результат преобразования в столбце D.
Задача 2. Разделение текстовых строк
После импорта данных сотрудников их фамилии, имена и отчества оказались в одном столбце. Необходимо разделить данные по столбцам .
Решение. Задача сложнее предыдущей и для ее реализации понадобится несколько текстовых функций.
Для отделения фамилии сотрудника и запишем в ячейку B1 формулу
=ЛЕВСИМВ(A1;НАЙТИ(» «;A1))
В этой формуле количество выводимых знаков подсчитывается функцией Найти , которая определяет местонахождение первого пробела в строке.
Для записи имени в ячейку C1 запишем следующую формулу
=ПСТР(A1;НАЙТИ(» «;A1)+1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1)
Если посмотреть на синтаксис записи данной функции, то получаем:
- Выражение НАЙТИ(» «;A1)+1 отвечает поиск позиции первого пробела в текстовой строке. А чтобы получить позицию первой буквы имени, прибавляется единица.
- Для определения количества символов в имени используется конструкция посложнее ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1. Количество символов определяется как разность позиций пробелов, отделяющих имя. Чтобы исключить из числа найденных символов сами пробелы, в начальной позиции прибавляется единица, а потом из полученного результата вычитается единица.
Отчество получается в ячейке D1 по более сложной формуле
=ПСТР(A1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1;ДЛСТР(A1)-ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1)
Здесь количество знаков в отчестве определяется как разность общего количества символов ( ДЛСТР) и позицией второго пробела.
В рассмотренных примерах функции ПОИСК и НАЙТИ выполняют одинаковые операции, так как разница в регистрах символов не учитывается. Возможно обойтись только одной из них.
Задача 3. Укорачивание текстовых строк
В список сотрудников внести изменения. Записать в одном столбце Фамилии и инициалы.
Решение. В зависимости от исходного состояния списка возможны два варианта.
1 вариант. Исходные данные содержатся в одном столбце. ФИО разделены одинарным пробелом.
Записываем следующую формулу
=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1));ПСТР(A1;НАЙТИ(» «;A1);2);».»;ПСТР(A1;НАЙТИ(» «;A1; НАЙТИ(» «;A1)+1);2);».»)
2 вариант. Исходные данные содержатся в разных столбцах.
Формула для преобразования
=СЦЕПИТЬ(A1;» «;ЛЕВСИМВ(B1);».»;ЛЕВСИМВ(C1);».»)
Друзья, если вы можете привести свои примеры использования текстовых функций в Excel, расскажите о них в комментариях. Рекомендую посмотреть статью о построении графиков функций в Excel . Понравилась статья, поделитесь с друзьями. Кнопочки социальных сетей внизу.
Дополнительные статьи по теме:
