Разделение текста на части формулой. Часть 1.
Во время работы с программой Excel встречаются ситуации, когда текст из ячейки требуется разделить на составляющие. Простейший пример – когда содержащиеся в одной графе фамилию, имя и отчество требуется разнести по отдельным колонкам таблицы. Как разрешить такую задачу? Обычно в таких случаях используется инструмент «текст по столбцам», который вызывается соответствующей кнопкой на вкладке “ДАННЫЕ”. Применить инструмент несложно. Вначале выбираем ячейки со строками для разделения и нажать на кнопку «текст по столбцам» на вкладке «Данные».
Затем указываем способ разделения (формат данных) – по разделителю или заданной ширины. В первом случае задаем символ, используемый разделителем. Выбираем разделитель среди стандартных или назначаем разделитель сами.
Во втором случае передвигаем ползунки на линейке сверху образца разбора данных, чтобы выбрать нужное количество знаков. При необходимости добавляем или удаляем ползунки щелчком по линейке.
Наконец, на третьем шаге разбиения при необходимости уточняем формат получившихся частей, а также указываем размещение полученных колонок.
В итоге получаем результат, сходный с изображением ниже
Итог работы инструмента на первый взгляд выглядит удачно, но на практике такое разбиение часто бесполезно по той или иной причине.
Начнем с того, что содержимое обрабатываемых колонок редка содержит равное количество частей, если не учитывать работу с ФИО сотрудников. Соединять же заново между собой часть строки для каждой позиции занятие неблагодарное, к тому же количество таких частей неодинаково. Такое разбиение приводит вовсе не к тому результату, который ожидался. На следующем примере стоит задача отделить ИИН и наименования УГД.
Применение инструмента «Текст по столбцам» проблему не решит.
Как видно, мало того, что пошло разделение по отдельным столбцам, что не нужно, так еще и исчезли значения ИИН.
Во-вторых, применение инструмента внутри таблицы приводит к удалению части содержимого исходной таблицы. Вот таблица до разделения графы товар на части – артикул и наименование номенклатуры.
.
А вот – после разделения.
Задать заранее количество столбцов и вставить нужное количество дополнительных колонок в таблицу часто невозможно, так как неизвестно количество частей, на которые будет разделена ячейка. Да и собирать заново наименование номенклатуры, но уже без артикула проблематично.
И вот тут для извлечения получить произвольной части содержимого строки на помощь придут формулы и функции. На текущем занятии рассмотрим деление текста на части формулой, способы извлечения первого и последнего элементов строки, разделение слипшегося текста.
Разберем простейший случай. Известно количество знаков, которое надо извлечь из изначального текста. Эта задача решается применением функций ЛЕВСИМВ и ПРАВСИМВ. Эти функции извлекают нужное количество знаков соответственно с левой и с правой стороны исходного текста, отсюда и названия. Синтаксис приведенных функций одинаков:
=ЛЕВСИМВ(текст;кол-во знаков для извлечения)=ЛЕВСИМВ(текст;кол-во знаков для извлечения)
=ПРАВСИМВ(текст;кол-во знаков для извлечения)
Стоит учесть, что количество знаков указывать необязательно. Если это не сделать, то функции возьмут с левой или правой стороны только первый знак. Если же количество знаков, заданное функцией, превысит количество знаков обрабатываемой строки, то в качестве результата получим исходную строку. Снова рассмотрим задачу по УГД и ИИН, рассмотренную выше. Очевидно, что ИИН располагается в конце содержимого ячеек. С другой стороны, известно, что ИИН содержит 12 знаков. Учитывая, что первая строка для обработки извлекается из ячейки А1, получаем несложную формулу из одной функции: =ПРАВСИМВ(A1;12)
Так как результат работы указанных функции текстовая строка, то нули в начале ИИН не исчезли. Теперь, зная ИИН, определяем год рождения или регистрации налогоплательщика. Это первые две цифры ИИН. Первый найденный ИИН лежит в ячейке В1, таким образом формула для нахождения последних чисел года рождения или регистрации примет вид
=ЛЕВСИМВ(B1;2)
Рассмотренные функции можно и нужно объединять одной формулой. Представим, что требуется определить пятую цифру полученного работой функции ПРАВСИМВ в примере найденного выше значения ИИН. Если это число от 4 и выше, то перед нами не ИИН, а БИН, и мы имеем дело с юридическим лицом. Поступаем следующим образом. Вначале получаем в ячейке С1 из полученного значения первые 5 знаков с помощью ЛЕВСИМВ
=ЛЕВСИМВ(D1;5)
Затем, используя полученное значение из ячейки C1 получаем последнюю его цифру, точнее символ, с помощью ПРАВСИМВ
=ПРАВСИМВ(С1)
Объединив обе функции и используя во второй из них вместо адреса С1 находящуюся в ячейке формулу, получаем итоговый вариант
=ПРАВСИМВ(ЛЕВСИМВ(D1;5))
На приведенных примерах видно что, если количество знаков первого или последнего слова известно, извлечение не представляет сложности. На практике точное количество знаков искомого слова заранее неизвестно. Поэтому используем составную формулу, дополнительно применив функции, перечисленные ниже.
· ПОИСК или НАЙТИ. Находят позицию, с которой начинается заданный текст внутри указанной строке. Различие в том, что функция НАЙТИ чувствительна к регистру. Для указанных функций задаем следующие параметры:
1. искомый текст.
2 строка, внутри которой идет поиск.
3. Позиция, начиная с которой начнется поиск. Если этот параметр не задан, поиск пойдет с самого начала.
· ДЛСТР – показывает длину строки. Параметром функции задаем либо саму строку, либо ячейку, которая содержащее обрабатываемую строчку.
Рассмотрим применение приведенных функций на примере таблицы «Склад».
Пусть требуется разделить графу «Товар» на две части. Первая послужит для вывода артикул атовара, во вторую занесем его наименование. Код товара и наименование разделяются пробелом. Воспользуемся этим для нахождения артикула. Определить расположение первого пробела внутри ячейки можно с помощью функции ПОИСК. Применение функции НАЙТИ в этом случае нежелательно –в дальнейшем возможна проблема с регистром искомого текста.
Отняв от номера найденной позиции единицу, получаем количество символов до введенного пробела. Эти символы и составляют первое слово, в нашем случае – код, или артикул, товара. Наконец, используя найденную длину слова и функцию ЛЕВСИМВ, находим само слово. Итоговая формула, с учетом того, что графа «товар» это колонка A и значение начинаются с ячейкиА2, выглядит так.
=ЛЕВСИМВ(A2;ПОИСК(” “;A2)-1)
На изображении ниже дополнительно показан порядок срабатывания функций и формул для достижения результата..
екстом в Excel, раделение текста по ячейкам, делим текст на чсасти, обучение Excel, практика раб
В следующем примере извлечем наименование товара. Это текст, расположенный после первого пробела. Применить предыдущую формулу, поменяв название ЛЕВСИМВ на ПРАВСИМВ, нельзя, так как рассмотренная выше формула вычисляет количество знаков до первого пробел, требуется же найти количество знаков после пробела. Чтобы это сделать, найдем разницу между количеством знаков в исходной строке и номер позиции, на которой расположен в этой строке первый пробел. В итоге получаем такую запись формулы.
=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(” “;A2))
Рассмотрим последовательно работу формулы.
- На первом шаге функция ДЛСТР определяет количество знаков из ячейки А2.
- На втором шаге функция ПОИСК находит позицию, на которой расположен первый пробел.
- Шаг третий – нахождение разницы между количеством знаков и позицией первого пробела
- Наконец, на четвертом шаге берем найденное количество знаков с правой стороны исходной строки из ячейки А2, получая тем самым наименование номенклатуры товара.
При просмотре результата работы формулы видно, что часть наименований сдвинута относительно края ячейки. Это связано с тем, что в некоторых ячейках из исходной графы товар после кода товара ввели больше одного пробела, и лишние пробелы перешли вместе с названием товара. Чтобы убрать полученный эффект с лишними пробелами, обернем полученную формулу функцией СЖПРОБЕЛЫ. Функция удаляет каждый пробел в самом начале, в самом конце, а также пробелы, кроме одного, между словами в строке. Окончательный вариант формулы и результат вычислений показаны ниже
формула:
=СЖПРОБЕЛЫ(ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(” “;A2)))
результат:
Встречаются ситуации, когда символ, разделяющий части текста , нельзя напрямую вставить с клавиатуры. Пример – вставка символа разрыва строки. Визуально он не виден, поэтому создается впечатление слитого текста. Именно так выглядит графа «Аналитика дебета» в таблице ниже.
Обратите внимание, что название УГД и фамилия плательщика слиты вместе. На самом деле здесь находится знак разрыва строки. Ввести его с клавиатуры нельзя, так как просто произойдет принудительный переход.
В таких случаях вместо явного ввода символа используют функцию СИМВОЛ. Она выводит символ по его коду. У знака разрыва строки код равен десяти. Поэтому по аналогии с формулой получения первого слова для извлечения наименования УГД извлечем все символы до знака разрыва строки. Получаем формулу:
=ЛЕВСИМВ(B2;ПОИСК(СИМВОЛ(10);B2)-1)
Здесь В2 – первая ячейка таблицы для извлечения наименования УГД.
На следующем этапе рассмотрим способы получения последнего слова в строке, а точнее – набора знаков после указанного разделителя. В обычном тексте это пробелы, но это может быть и дефис, и двоеточие, и определенный текст. В интернете можно найти различные формулы для решения этой задачи. Например – такой вариант
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(” “&ПОДСТАВИТЬ(A1;” “;ПОВТОР(” “;999));999*1);1;999);” “;””)
На самом деле все они связаны с комбинированием уже известных нам функций ПРАВСИМВ, ДЛСТР и СЖПРОБЕЛЫ, а также функций ПОДСТАВИТЬ и ПОВТОР, о которых мы сейчас и поговорим.
Функция ПОДСТАВИТЬ позволяет заменить в указанной строчке другим. Ее параметры:
1. Исходная строка для обработки
2. Текст, который надо убрать
3. Текст, добавляемый вместо удаляемого текста
4. позиция для вставки.
Позиция для вставки определяет место, в котором текст подлежит замене – на первом вхождении в строке, на втором и так далее. Если ее не указать, то замена произойдет везде. Учтите , что эта функция чувствительна к регистру!
Функция ПОВТОР повторяет заданный символ или текст нужное количество раз. Параметры для функции ПОВТОР
1. повторяемый текст.
2. Количество повторов.
Предположим, что строка, из которой надо получить последнее слово, находится в ячейке В2. Делаем следующие шаги, последовательно усложняя формулу и получая поэтапные формулы
1. Узнаем количество знаков в строке
=ДЛСТР(В2)
2. создаем строку из пробелов, длина которой равна количеству знаков в строке. Используем функцию ПОВТОР
=ПОВТОР(« »; ДЛСТР(В2)). Еще раз подчеркиваем, что первым аргументом тут выступает пробел, указанный в кавычках.
3. заменяем все пробелы в исходной строкой, которую мы создали. Применим функцию ПОДСТАВИТЬ.
=ПОДСТАВИТЬ(В2; « »; ПОВТОР(« »; ДЛСТР(В2)).
Наверное, это ключевой момент. Теперь последнее слово отделено от основной строки количеством пробелов, равным количеству знаков в исходной строке.
4. Из полученной строки возьмем символы справа, то есть с конца. Количество извлекаемых символов сделаем равным длине исходной строки
=ПРАВСИМВ(ПОДСТАВИТЬ(В2; « »; ПОВТОР(« »; ДЛСТР(В2)));ДЛСТР(В2))
Получаем строку, начало которой содержит пробелы, а конец – последнее слово из исходной строки.
5. Убираем лишние пробелы функцией СЖПРОБЕЛЫ и получаем нужный результат – последнее слово из строки
=СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(B2;” “;ПОВТОР(” “;ДЛСТР(B2)));ДЛСТР(B2)))
Вместо функции ДЛСТР можно просто указать произвольное количество знаков, например 100 или 900. Тогда формула станет короче. В примере ниже берем 10 знаков.
=СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(В2; « »; ПОВТОР(« »; 10)));10)))
Однако если последнее слово имеет больше символов, чем заданное число, то получим проблему.
Вот пример работы первой формулы
А вот так сработала здесь же вторая формула
Конечно, допустимо заранее подставить заведомо большое число, например, 999, но лучше не рисковать.
На этом наше занятие по изучению на практике приемов разделения текста и способов получения первого и последнего слова в строке подошло к концу. Учебный файл для самостоятельной тренировки доступен по ссылке. На следующем уроке по обработке текста бы разберем приемы извлечения второго, третьего и других слов, находящихся в середине указанной строки.
Желаем всем хорошего дня и успешной работы. До встречи на занятиях в Учебном центре «Зерде»!
