ДЛСТР(), НАЙТИ()
Обычно для объяснения функций
ДЛСТР и НАЙТИ приводят пример с получением второго или n-ного слова из
текстовой строки. Но на деле довольно редко приходится сталкиваться с задачей в
которой бы пригодилось умение извлекать второе по порядку слово (разве что из
ФИО, но для этого у Excel уже имеется готовый и более удобный инструмент
"Текст по столбцам").
Поэтому мы рассмотрим другой
пример с использованием этих функций - возможно довольно сложный, но который
точно может Вам пригодится при работе с текстовыми строками.
Итак дана выписка со счёту
клиента за последние два года (скриншот №1). Необходимо получить из столбца
"Е" номера платёжных поручений по которым поступали средства на счёт
клиента или же переводились с этого счёта на другие счета.
Так как средства на счёт клиента
поступали с разных источников, соответственно и номера платёжных поручений не
одинаковы; некоторые из них пятизначны, некоторые шестизначны, некоторые
содержат дроби. Единственное что у них общее так это знак "№". От
этого символа и будем отталкиваться.
Для начала узнаем позицию символа
"№" в каждой текстовой строке. Для этого используем функцию НАЙТИ,
синтаксис которой НАЙТИ(искомый_текст; просматриваемый_текст;[нач_позиция]).
Как видите с помощью этой функции можно искать не только отдельный символ но и
целое слово. Находим позицию символа "№" в каждой строке с помощью
формулы =НАЙТИ("№";E2) (скриншот №2).
Теперь, зная позицию символа
"№" в строке, мы можем извлечь ту часть строки, которая начинается с
этого символа, используя функцию ПРАВСИМВ. Если Вы помните самый первый пост,
синтаксис функции ПРАВСИМВ(текст;число_знаков). Для нахождения числа_знаков
отнимем позицию символа "№" от общего количества символов строки:
Длина строки - позиция символа "№"=число_знаков . Длина строки
находится с помощью функции ДЛСТР(текст). В итоге у нас получается формула =ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)) (скриншот №3).
Окей, нам удалось вычленить часть
текста начиная с символа "№". Но, нам нужен только номер платежного
поручения а все остальные слова идущие после этого номера тоже лишние.
Посмотрим внимательно на текст в
столбце "G". Как видите текст в каждой строке данного столбца
начинается с номера платежного поручения а потом идёт пробел и уже другие
слова. Следовательно, мы можем используя функцию ЛЕВСИМВ (текст; число_знаков) вычленить
номер платежного поручения, если найдём позицию в тексте первого пробела. В
итоге мы получаем номер платежного поручения, используя формулу =ЛЕВСИМВ(G2;НАЙТИ(" ";G2)) (скриншот №4).
Итак, мы смогли справиться с
поставленной задачей и вычленить номера платежных поручений из каждой строки.
Но, вместо одной формулы мы использовали две (в столбце "G" и столбце
"H"), А ЭТО НЕ ЕСТЬ КРУТО. Как же мы можем вычленить номер платежного
поручения используя всего лишь одну формулу?
Присмотримся к нашей последней
формуле =ЛЕВСИМВ(G2;НАЙТИ("
";G2)). Как видим в качестве текстового
параметра она ссылается на значение ячейки "G2", которое само
является результатом формулы =ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)). Следовательно, мы можем объединить эти две формулы в одну,
заменив в последней формуле "G2" на ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2). Сделав это, получаем комбинированную формулу =ЛЕВСИМВ(ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2));НАЙТИ("
";ПРАВСИМВ(E2;ДЛСТР(E2)-НАЙТИ("№";E2)))) (скриншот №5).
Полученная итоговая формула также
может быть использована для вычленения паспортных номеров из строк с
паспортными данными, названий городов из строк с адресами клиентов, номеров
банкоматов и терминалов из выписок с карточных счетов.
Подписаться на:
Сообщения
(
Atom
)
Сообщения
