Работа с текстовыми строками — важная часть обработки информации в Excel. С ними удобно работать вручную, если общее количество относительно невелико. Но как только количество строк становится приличным (например, одна или даже несколько сотен), операции с ними становятся довольно сложными.
Слава Богу, в арсенале Excel есть набор инструментов, позволяющих работать со строками текстового формата и автоматизировать большое количество процессов, связанных с ними. Сегодня мы рассмотрим их более подробно.
Как в Excel сделать разделение строки на подстроки
Есть несколько способов сделать это. Во-первых, это можно сделать с помощью текстовых функций. Самый популярный из них — PSTR, но на самом деле их намного больше. С их помощью можно реализовать практически любую идею, которая придет в голову или будет пущена в ход руководством.
Для достижения этой цели также можно использовать макросы. Для этого в VBA есть специальная функция — Split. Он разделяет строку разделителями, которые могут быть как конкретным символом, так и несколькими одновременно. Синтаксис функции включает три аргумента, из которых требуется только один.
- Выражение. Это строка, которую нужно разбить на подстроки.
- Разделитель. Ограничение. Этот аргумент является необязательным. Если вы не введете в него никаких значений, по умолчанию в качестве разделителя будет принят пробел.
- Лимит. Количество подстрок, на которые нужно разделить ввод. Этот аргумент также является необязательным. В этом случае значение по умолчанию будет равно -1.
- Сравнивать. С этим аргументом функции передается тип сравнения - бинарное или текстовое. Проще говоря, в первом случае (если тип сравнения установлен на 0) функция при сравнении чувствительна к регистру. При текстовом сравнении регистр букв не учитывается.
Значение, которое вернет эта функция, представляет собой массив, показывающий подстроки, количество которых задается параметром limit. Внимательному читателю может быть интересно, если установить значение -1, функция вернет все подстроки. А теперь давайте приведем несколько примеров того, как работает эта функция VBA.
Подтест1()
Dim a () как строка
a = Split("время не ждать»)
MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)
Конец сабвуфера
Эта функция выводит предупреждение, которое выдает три подстроки «время не ждать». В этом случае используются настройки по умолчанию. Если написать такой код, то в алерте будет отображаться строка "время не-ждет" из исходной строки в такую же, только вместо пробела используется дефис.
Подтест2()
Dim a () как строка
a = Split("время-не-ждет","-", 2)
MsgBox a(0) и vbNewLine & a(1)
Конец сабвуфера
Здесь используются значения аргумента Delimiter в -, а Limit - 2. Таким образом можно было бы разбить все на три части, но так как мы указали только две, то видим, что конечный результат тоже одна подстрока "время" и подстрока "не-ждет". Мы видим, что все на самом деле невероятно просто.
Текстовые функции в Эксель
Все функции, предназначенные для работы с текстом, находятся в соответствующем разделе мастера функций. Их много. Выберем среди них те, которые чаще всего используются для решения прикладных задач:
- БАТТЕКСТ(Значение). Функция, необходимая для преобразования ячейки числового формата в текст. Это полезно, если для формулы требуется текстовое значение, а число в ячейке представлено в виде числового значения. С помощью этой функции вы можете конвертировать данные из одного типа в другой.
- DLSTR(значение). Эта функция позволяет определить длину строки и количество символов в ней. Возвращает число, равное количеству символов, записанных в этой строке.
- ЗАМЕНИТЬ (старый текст, начальная позиция, количество символов, новый текст). С помощью этой функции вы можете заменить один текст другим, используя в качестве ориентира определенное количество символов, начиная с позиции, указанной пользователем.
- ЗНАЧЕНИЕ(текст). Эта функция выполняет операцию, обратную первому оператору — преобразует значение текстового формата в числовой.
- LEFT(строка, количество символов). Эта функция позволяет вам получить указанное пользователем количество символов из строки, указанной человеком. При этом учитываются знаки, расположенные слева.
- ПРАВО(строка, количество символов). Принцип работы этой функции аналогичен, только с ее помощью можно вернуть вправо определенное количество символов. То есть выяснить, какой должна быть часть строки, и начать с самого последнего символа.
- НАЙТИ(текст для поиска, текст для поиска, начальная позиция). С помощью этой функции вы можете получить позицию, в которой находится текст, указанный пользователем. Этот оператор можно использовать только в том случае, если дело важно для нас. Если нет разницы, какие буквы использовать: прописные или строчные, то есть аналогичная функция - ПОИСК. Также следует отметить, что эта функция вернет только первый экземпляр, все последующие экземпляры не учитываются. Для этого есть другие функции.
- ЗАМЕНИТЬ(текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то он похож на оператор REPLACE, но имеет более широкий функционал. Если пользователь не указал последний аргумент, заменяются все вхождения в тексте. Так что это позволяет вам автоматизировать параметры Excel «Заменить все».
- ПОДСТРОКА(текст, разделитель, число). С помощью этой функции вы можете получить строку, разделенную разделителем.
- PSTR (текст, начальная позиция, количество символов). Это одна из важнейших функций, которую мы сегодня подробно разберем. В чем-то он похож на LEFT, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
- СЦЕПИТЬ(Текст1, Текст2...). Это функция, которая позволяет вам конкатенировать несколько строк. Это своего рода замена оператору &. Максимальное количество линий, которые можно соединить друг с другом, равно 30.
Принцип для многих из этих функций аналогичен. Поэтому, когда вы выучите один из них, вам будет намного легче выучить следующие. И когда вы начнете применять их на практике, они будут изучены автоматически. Опишем реальный пример использования текстовых функций.
Пример использования текстовых функций в Эксель
Опишем некоторые практические применения текстовых функций. Для наглядности представим работу функции SUBSTRING и решаемую задачу. Первый столбец в этой таблице — это вся строка. Второе — это значение, которое нам нужно найти в первом столбце. В третьем столбце перечислены формулы, которые можно использовать для этого.
Функция может ссылаться на ячейку в каждом из своих аргументов. Например, номер подстроки можно найти по определенному адресу. В этом случае формула будет выглядеть так.
И в этом примере мы попробуем разбить номер телефона на несколько частей.
Недостатком функции ПОДСТРОКА является то, что она требует разделителя, поэтому вы можете отделять только слова друг от друга или цифры в телефонном номере.
Если вам нужно отделить одно слово от другого, вы можете использовать разделитель в виде пробела. В этом случае необходимо открыть кавычку, вставить пробел, а затем закрыть кавычку в соответствующем аргументе.
Синтаксис функции ПСТР в Excel
Функция MID в Excel чаще всего используется для того, чтобы получить часть строки и использовать ее в дальнейших вычислениях или просто записать в ячейку. Причина популярности этой функции проста — когда имеется большое количество информации, импортированной из других программ, вам часто приходится извлекать часть ее вручную. А с помощью этой функции можно хоть немного автоматизировать процесс. Давайте рассмотрим эту функцию более подробно.
Он предоставляет три аргумента, каждый из которых является обязательным: текст для обрезки, где начать обрезку и где остановиться. Источником данных для обработки может быть текст, набранный в ячейке таблицы, а также сгенерированный по другой формуле. Так как нам нужно получить подстроку, нам нужно указать следующие аргументы:
- Текст. Текстовая строка, из которой мы получим «усеченную» версию. Помимо результата функции и ссылки на ячейку, в качестве параметра для этого аргумента может выступать и текстовая константа. Но на практике его использование лучше всего подходит для тренировок. В реальной жизни в этом нет необходимости, так как вы всегда можете вручную вставить нужный фрагмент текста в любую ячейку.
- Начальная позиция. Счетчик символов для этого аргумента начинается с самого первого символа слева от строки. Эта функция отличается от некоторых других тем, что символы отсчитываются с цифры 1, а не с нуля.
- Количество символов. Здесь записывается общее количество символов, которое нам нужно отсчитать от начальной позиции. Минимальное значение равно 1. Гипотетически в качестве значения этого аргумента можно указать 0, но в этом случае результатом будет пустая строка.
В самом общем виде формула выглядит так: =MID(текст; начальная позиция; число_символов)
Есть еще вариант этой формулы: PSTRB, который позволяет работать с многобайтовыми строками. Но таких формул в нашем языке нет, поэтому достаточно просто знать, что такая формула существует. Есть два возможных результата после запуска этой формулы:
- Ошибка. Если аргументы функции были указаны неверно, отображается ошибка #ЗНАЧ!. Типичными причинами этой ошибки являются начальная позиция нуля или отрицательное значение в аргументе "Количество символов».
- Линия. Если все параметры были указаны правильно, мы получаем окончательную текстовую строку.
Вот некоторые вещи, которые следует учитывать при использовании этой функции:
- Параметр начальной позиции не может быть больше, чем общее значение строки. В противном случае в качестве результата функции будет возвращена строка нулевой длины (то есть пустая).
- Если, даже если значение «начальная позиция» меньше общей длины строки, сумма значений «начальная позиция» и «количество символов» больше, чем общее количество символов в этом тексте, функция возвращает оставшиеся символы из указанной позиции. Таким образом, вы можете указать в качестве количества символов заведомо большое число, чтобы функция возвращала символы, расположенные в крайнем правом конце строки.
- Ошибка № ЗНАЧ! происходит в следующих ситуациях: если начальная позиция меньше единицы, количество символов или количество байтов (для функции PSTRB) отрицательное.
Вам может быть интересна функция STRB только в том случае, если вы ведете электронную таблицу Excel на японском, китайском и корейском языках. В этом случае некоторые символы занимают в памяти более одного байта.
Подстрока из строки в Эксель при помощи функции ПСТР
Давайте рассмотрим небольшой пример того, как извлечь отдельные символы из строки с помощью функции MID. Возьмем очень простую ситуацию. Предположим, у нас есть простая строка в ячейке B14, которая состоит из последовательности чисел от 1 до 0. Предположим, нам нужно получить тройку из строки 1234567890. В этом случае формула должна быть: = MID(B14;3; 1).
Простыми словами мы говорим программе, что нам нужно получить один символ из этой последовательности значений, начиная с третьего числа в этой строке. Хотя он кажется числовым, в нашем примере это текст. После того, как мы отдадим эти команды программе, на выходе мы получим цифру 3. Когда может возникнуть потребность в возможности решить именно такую задачу? Прежде всего, когда у нас есть набор символов на одной строке и нам нужно взять оттуда определенные символы.
Оцените качество статьи. Ваше мнение важно для нас.
