Excel текст с определенной позиции

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

Примеры функции ТЕКСТ в Excel

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

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

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

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

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

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

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

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «—»:

Обратите внимание, что значения теперь отображаются в числовом формате.



Функция разделения текста в Excel

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

• ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
• ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
• ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

Далее из общей длины строки отнимается количество знаков с начала строки до второго пробела. Результат – число символов справа, которые нужно вернуть.

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

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

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

С помощью кавычек в формуле можно добавить в конечное выражение любой знак или текст.

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Скачать пример функции для работы с текстом в Excel

Читайте так же: как перевести число и сумму прописью.

Синтаксис функции ПОИСК:

• «искомый текст» — что нужно найти;
• «просматриваемый текст» — где искать;
• «начальная позиция» — с какой позиции начинать искать (по умолчанию – 1).

Если нужно учитывать регистр, используется функция НАЙТИ.

Как извлечь часть текста в Эксель

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

Для получения части текста, в Эксель есть 3 функции:

1. ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Функция ЛЕВСИМВ в Эксель

1. ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .

Функция ПРАВСИМВ в Excel

1. ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).

Функция ПСТР в Эксель

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

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

А следующий пост мы посвятим поиску нужного текста в строке. Заходите и читайте. Только хорошее владение функциями позволит вам эффективно выполнять задачи в Microsoft Excel!

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

ПСТР, ПСТРБ (функции ПСТР, ПСТРБ)

В этой статье описаны синтаксис формулы и использование функций ПСТР и ПСТРБ в Microsoft Excel.

Функция ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Функция ПСТРБ возвращает определенное число знаков из текстовой строки, начиная с указанной позиции, на основе заданного числа байтов.

Эти функции могут быть доступны не на всех языках.

Функция ПСТР предназначена для языков с однобайтовой кодировкой, а ПСТРБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

Функция ПСТР всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

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

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Аргументы функций ПСТР и ПСТРБ описаны ниже.

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

Начальная_позиция Обязательный. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Число_знаков Обязательный. Указывает, сколько знаков должна вернуть функция ПСТР.

Число_байтов Обязательный. Указывает, сколько знаков должна вернуть функция ПСТРБ (в пересчете на байты).

Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).

Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.

Если значение «начальная_позиция» меньше 1, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Если значение «число_знаков» отрицательно, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Если значение «число_байтов» отрицательно, функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Разбор текстовых строк в MS EXCEL

Часто текстовая строка может содержать несколько значений. Например, адрес компании: «г.Москва, ул.Тверская, д.13», т.е. название города, улицы и номер дома. Если необходимо определить все компании в определенном городе, то нужно «разобрать» адрес на несколько составляющих. Аналогичный подход потребуется, если необходимо разнести по столбцам Имя и фамилию, артикул товара или извлечь число или дату из текстовой строки.

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

Самый простейший случай, если адрес, состоящий из названия города, улицы и т.д., импортирован в ячейку MS EXCEL из другой информационной системы. В этом случае у адреса имеется определенная структура (если элементы адреса хранились в отдельных полях) и скорее всего нет (мало) опечаток. Разгадав структуру можно быстро разнести адрес по столбцам. Например, адрес «г.Москва, ул.Тверская, д.13» очевидно состоит из 3-х блоков: город, улица, дом, разделенных пробелами и запятыми. Кроме того, перед названием стоят сокращения г., ул., д. С такой задачей достаточно легко справится инструмент MS EXCEL Текст по столбцам. Как это сделать написано в статье Текст-по-столбцам (мастер текстов) в MS EXCEL.

Очевидно, что не всегда адрес имеет четкую структуру, например, могут быть пропущены пробелы (запятые все же стоят). В этом случае помогут функции, работающие с текстовыми строками. Вот эти функции:

— Функция ЛЕВСИМВ() в MS EXCEL — выводит нужное количество левых символов строки;

— Функция ПРАВСИМВ() в MS EXCEL — выводит нужное количество правых символов строки;

— Функция ПСТР() в MS EXCEL — выводит часть текста из середины строки.

Используя комбинации этих функций можно в принципе разобрать любую строку, имеющую определенную структуру. Об этом смотри статью Разнесение в MS EXCEL текстовых строк по столбцам.

Еще раз отмечу, что перед использованием функций необходимо понять структуру текстовой строки, которую требуется разобрать. Например, извлечем номер дома из вышеуказанного адреса. Понятно, что потребуется использовать функцию ПРАВСИМВ(), но сколько символов извлечь? Два? А если в других адресах номер дома состоит из 1 или 3 цифр? В этом случае можно попытаться найти подстроку «д.», после которой идет номер дома. Это можно сделать с помощью функции ПОИСК() (см. статью Нахождение в MS EXCEL позиции n-го вхождения символа в слове). Далее нужно вычислить количество цифр номера дома. Это сделано в файле примера , ссылка на который внизу статьи.

Усложним ситуацию. Пусть подстрока «д.» может встречаться в адресе несколько раз, например, при указании названия деревни используется сокращение «д.», т.е. совпадает с префиксом номера дома. В этом случае нужно определить все строки, в которых имеется название деревень (первые 2 символа, т.к. это адрес населенного пункта) и исключить их. Также можно извлечь все цифры из строки в отдельный диапазон (см. статью Извлекаем в MS EXCEL число из конца текстовой строки). Но, что делать, если в названии улицы есть числа? Например, «26 Бакинских комиссаров». Короче, тут начинается творчество.

Не забудьте про пробелы! Каждый пробел — это отдельный символ. Часто при печати их ставят 2 или 3 подряд, а это совсем не то же самое, что один пробел. Используйте функцию Функция СЖПРОБЕЛЫ() в MS EXCEL, чтобы избавиться от лишних пробелов.

Об извлечении названия файла из полного пути см. Извлечение имени файла в MS EXCEL.

Часто в русских текстовых строках попадаются английские буквы. Их также можно обнаружить и извлечь, см. Есть ли в слове в MS EXCEL латинские буквы, цифры, ПРОПИСНЫЕ символы.

Все статьи сайта, связанные с преобразованием текстовых строк собраны в этом разделе: Изменение Текстовых Строк (значений).

Артикул товара

Пусть имеется перечень артикулов товара: 2-3657; 3-4897; .

Как видно, артикул состоит из 2-х числовых частей, разделенных дефисом. Причем, числовые части имеют строго заданный размер: первое число состоит из 1 цифры, второе — из 4-х.

Функции Excel для работы с элементами строк

Следующие функции находят и возвращают части текстовых строк или составляют большие строки из небольших: НАЙТИ (FIND), ПОИСК (SEARCH), ПРАВСИМВ (RIGHT), ЛЕВСИМВ (LEFT), ПСТР (MID), ПОДСТАВИТЬ (SUBSTITUTE), ПОВТОР (REPT), ЗАМЕНИТЬ (REPLACE), СЦЕПИТЬ (CONCATENATE).

Функции НАЙТИ и ПОИСК

Функции НАЙТИ (FIND) и ПОИСК (SEARCH) используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа, с которого начинается первое вхождение искомой строки. Эти две функции работают одинаково за исключением того, что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона. Функции имеют следующий синтаксис:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
=ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

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

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

Например, чтобы определить позицию буквы «ж» в строке «Гаражные ворота», надо использовать формулу:

Эта формула возвращает 5.

Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст символы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует одному произвольно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции. Например, чтобы найти позицию имен Анатолий, Алексей, Акакий в тексте, находящемся в ячейке А1, надо использовать формулу:

Функции ПРАВСИМВ и ЛЕВСИМВ

Функция ПРАВСИМВ (RIGHT) возвращает крайние правые символы строки аргумента, в то время как функция ЛЕВСИМВ (LEFT) возвращает первые (левые) символы. Синтаксис:

=ПРАВСИМВ(текст;количество_символов)
=ЛЕВСИМВ(текст;количество_символов)

Аргумент количество_символов задает число символов, извлекаемых из аргумента текст. Эти функции учитывают пробелы и поэтому, если аргумент текст содержит пробелы в начале или конце строки, в аргументах функций следует использовать функцию СЖПРОБЕЛЫ.

Аргумент количестов_символов должен быть больше или равен нулю. Если этот аргумент опускается, Excel считает его равным 1. Если количество_символов больше числа символов в аргументе текст, то возвращается весь аргумент.

Функция ПСТР

Функция ПСТР (MID) возвращает заданное число символов из строки текста, начиная с указанной позиции. Эта функция имеет следующий синтаксис:

=ПСТР(текст;нач_позиция;количество_символов)

Аргумент текст — это текстовая строка, содержащая извлекаемые символы, нач_позиция — это позиция первого символа, извлекаемого из текста (относительно начала строки), а количество_символов — это число извлекаемых символов.

Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Эти две функции заменяют символы в тексте. Функция ЗАМЕНИТЬ (REPLACE) замещает часть текстовой строки другой текстовой строкой и имеет синтаксис:

=ЗАМЕНИТЬ(старый_текст;нач_позиция;количество_символов;новый_текст)

Аргумент старый_текст — это текстовая строка, а которой надо заменить символы. Следующие два аргумента задают символы, которые нужно заменить (относительно начала строки). Аргумент новый_текст задает вставляемую текстовую строку.

Например, ячейка А2 содержит текст «Вася Иванов». Чтобы поместить этот же текст в ячейку А3, заменив имя, надо в ячейку А3 вставить следующую функцию:

В функции ПОДСТАВИТЬ (SUBSTITUTE) начальная позиция и число заменяемых символов не задаются, а явно указывается замещаемый текст. Функция ПОДСТАВИТЬ имеет следующий синтаксис:

=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

Аргумент номер_вхождения является необязательным. Он предписывает Excel заменить только заданное вхождение строки старый_текст.

Например, ячейка А1 содержит текст «Ноль меньше восьми». Надо заменить слово «ноль» на «нуль».

Число 1 в этой формуле указывает, что надо изменить только первое «о» в строке ячейки А1. Если аргумент номер_вхождения опущен, Excel заменяет все вхождения строки старый_текст на строку новый_текст.

Функция ПОВТОР

Функция ПОВТОР (REPT) позволяет заполнить ячейку строкой символов, повторенной заданное количество раз. Синтаксис:

=ПОВТОР(текст;число_повторений)

Аргумент текст — это размноженная строка символов, заключенная в кавычки. Аргумент число_повторений указывает, сколько раз нужно повторить текст. Если аргумент число_повторений равен 0, функция ПОВТОР оставляет ячейку пустой, а если он не является целым числом, эта функция отбрасывает десятичные знаки после запятой.

Функция СЦЕПИТЬ

Функция СЦЕПИТЬ (CONCATENATE) является эквивалентом текстового оператора & и используется для объединения строк. Синтаксис:

=СЦЕПИТЬ(текст1;текст2;. )

В функции можно использовать до 30 аргументов.

Например, ячейка А5 содержит текст «первое полугодие», следующая формула возвращает текст «Всего за первое полугодие»:

=СЦЕПИТЬ(«Всего за «;А5)

В начало страницы

В начало страницы

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

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

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

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

С ее помощью определяется длина строки. В качестве результата возвращается целое число, указывающее количество символов текста.

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

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

Пример использования:

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

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

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

Извлечь текст по позиции

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

1. Извлеките количество символов слева или справа

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

1.1 Извлеките первые или последние N символов с помощью формул

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

Извлечь первые N символов из текстовой строки

Функция ВЛЕВО может помочь легко извлечь первые N символов из текстовой строки в Excel.

Общая формула

=LEFT(text_string,[num_chars])

аргументы

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

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

=LEFT(B5,2)

Теперь первые 2 символа в каждой ячейке диапазона B5:B10 извлечены.

Извлечь последние N символов из текстовой строки

Здесь мы применяем функцию ПРАВИЛЬНО для извлечения последних N символов из текстовой строки в Excel.

Общая формула

=RIGHT(text_string,[num_chars])

аргументы

Выберите пустую ячейку, скопируйте или введите формулу ниже и нажмите Enter Ключ, чтобы получить результат. Затем выберите эту ячейку результата и перетащите ее маркер автозаполнения вниз, чтобы получить другие результаты.

=RIGHT(B5,2)

1.2 Извлеките первые или последние N символов с помощью замечательного инструмента

Хотя приведенные выше формулы просты, для извлечения первых или последних n символов из длинного списка текстовой строки вам все равно нужно перетаскивать дескриптор автозаполнения сверху вниз, что может занять немного времени. Здесь рекомендует Kutools for ExcelАвтора Извлечь текст утилита, помогающая извлечь первые или последние N символов из списка текстовых строк оптом.

1. Заранее выберите список текстовых строк, из которого вы хотите извлечь текст, и щелкните Кутулс > Текст > Извлечь текст.

2. Во всплывающем Извлечь текст диалоговое окно необходимо настроить следующим образом.

Внимание: Чтобы сделать результат динамическим при изменении текстовой строки, вы можете проверить Вставить как формулу пунктом.

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

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

Нажмите, чтобы узнать больше об этой функции.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

2. Извлечь текст до или после определенного символа/слова

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

2.1 Извлечь текст до или после первого разделителя (символа)

Как показано на снимке экрана ниже, чтобы извлечь текст до или после первого разделителя из каждой ячейки в диапазоне B4:B10, вы можете применить один из следующих методов.

2.1.1 Извлечь текст перед первым разделителем с формулой

Применение формулы, основанной на функциях ЛЕВЫЙ и НАЙТИ, может помочь извлечь текст перед первым разделителем из ячейки. Вы можете выполнить следующие шаги, чтобы сделать это.

Общая формула

=LEFT(text_string,FIND(«delimiter»,text_string,1)-1)

аргументы

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

=LEFT(B5,FIND(«-«,B5,1)-1)

2.1.2 Извлечь текст после первого разделителя с формулой

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

Общая формула

=MID(text_string,FIND(«delimiter»,text_string)+1,LEN(text_string))

аргументы

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

=MID(B5,FIND(«-«,B5)+1,LEN(B5))

2.1.3 Извлекайте текст до или после первого разделителя с помощью замечательного инструмента

Здесь настоятельно рекомендуется Извлечь текст полезности Kutools for Excel. С помощью этой функции вы можете легко извлекать тексты до или после первого разделителя из диапазона ячеек.

1. Выберите диапазон ячеек, из которых вы хотите извлечь текст, а затем щелкните Кутулс > Текст > Извлечь текст.

2. в Извлечь текст диалоговое окно необходимо настроить следующим образом.

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

3. Затем еще один Извлечь текст появится диалоговое окно, выберите ячейку для вывода результатов и нажмите ОК.

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

Чтобы узнать больше об этой функции, посетите: Быстрое извлечение определенного текста из ячеек в Excel.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

2.2 Извлечение текста до или после последнего разделителя (символа)

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

2.2.1 Извлечь текст перед последним разделителем с помощью формулы

Чтобы извлечь текст перед последним разделителем из ячейки, вы можете использовать функции ПОИСК, ДЛСТР и ПОДСТАВИТЬ внутри функции ЛЕВЫЙ.

Общая формула

=LEFT(text_string,SEARCH(«#»,SUBSTITUTE(text_string,»delimiter»,»#»,LEN(text_string)-LEN(SUBSTITUTE(text_string,»delimiter»,»»))))-1)

аргументы

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

=LEFT(B5,SEARCH(«#»,SUBSTITUTE(B5,»-«,»#»,LEN(B5)-LEN(SUBSTITUTE(B5,»-«,»»))))-1)

2.2.2 Извлечь текст после последнего разделителя с формулой

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

Общая формула

=RIGHT(text_string,LEN(text_string)-SEARCH(«#»,SUBSTITUTE(text_string,»delimiter»,»#»,LEN(text_string)-LEN(SUBSTITUTE(text_string,»delimiter»,»»)))))

аргументы

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

=RIGHT(B5,LEN(B5)-SEARCH(«#»,SUBSTITUTE(B5,»-«,»#»,LEN(B5)-LEN(SUBSTITUTE(B5,»-«,»»)))))

2.3 Извлечь текст после n-го символа

Посмотрите на пример ниже, есть список текстовой строки в диапазоне B4:B10, чтобы извлечь текст после третьего символа из каждой ячейки, вы можете применить формулу, основанную на функции MID и функции LEN.

Общая формула

=MID(text_string,nth_char+1,LEN(text_string))

аргументы

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

=MID(B5,3+1,LEN(B5))

2.4 Извлечь n-е слово из текстовой строки

Предположим, у вас есть список текстовых строк, как показано на снимке экрана ниже, и вы хотите извлечь только n-е слово из текстовой строки. В этом разделе представлены три метода, как это сделать.

2.4.1 Извлечь n-е слово с помощью формулы

Вы можете комбинировать функции TRIM, MID, SUBSTITUTE, REPT и LEN, чтобы извлечь n-е слово из текстовой строки в ячейке.

Общая формула

=TRIM(MID(SUBSTITUTE(text_string,» «,REPT(» «,LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

аргументы

В этом случае диапазон B5:B10 содержит текстовые строки, D5:D10 содержит числа, представляющие n-е слово, давайте применим эту формулу для извлечения n-го слова из текстовой строки.

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

=TRIM(MID(SUBSTITUTE(B5,» «,REPT(» «,LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Примечание: Вы можете напрямую ввести n-е число в формулу следующим образом.

=TRIM(MID(SUBSTITUTE(B5,» «,REPT(» «,LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Извлечение n-го слова с помощью пользовательской функции

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

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте приведенный ниже VBA в окно кода.

Код VBA: извлечь n-е слово из текстовой строки в ячейке

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Примечание: в формуле D5 — это ячейка, содержащая число, представляющее n-е слово. Кроме того, вы можете напрямую заменить ссылку на ячейку числом.

5. Выберите ячейку результата и перетащите ее маркер автозаполнения вниз, чтобы извлечь n-е слово из текстовых строк других ячеек.

2.4.3 Извлеките n-е слово с помощью замечательного инструмента

Если вы не хотите вручную применять формулу или пользовательскую функцию, представленную выше, здесь рекомендуется Kutools for ExcelАвтора Извлечь n-е слово в ячейку полезность. С помощью этой функции вы можете легко извлечь n-е слово из текстовой строки в ячейке всего за несколько кликов.

1. Выберите ячейку для размещения результата и нажмите Кутулс > Формула Помощник > Текст > Выписка энное слово в ячейке. Смотрите скриншот:

2. в Помощник по формулам диалоговое окно необходимо настроить следующим образом.

3. Затем из текстовой строки в ячейке B5 извлекается n-е (второе) слово, и вы можете видеть, что в то же время создается формула. Выберите эту ячейку результата и перетащите ее маркер автозаполнения вниз, чтобы получить n-е слово из других текстовых строк.

Нажмите, чтобы узнать больше об этой функции.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

2.5 Извлечение текста до или после n-го вхождения разделителя

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

2.5.1 Извлечение текста перед n-м вхождением разделителя

Вы можете использовать функцию ВЛЕВО вместе с функциями ПОДСТАВИТЬ и НАЙТИ для извлечения текста перед n-м вхождением разделителя из ячейки в Excel.

Общая формула

=LEFT(SUBSTITUTE(text_string,»delimiter»,CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,»delimiter»,CHAR(9),n),1)-1)

аргументы

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

=LEFT(SUBSTITUTE(B5,» «,CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5,» «,CHAR(9),2),1)-1)

Примечание: В формуле B5 — это ячейка, содержащая текстовую строку, из которой вы хотите извлечь текст; « » здесь представляет собой пробел, а число 2 представляет второе вхождение пробела. Вы можете изменить их в соответствии с вашими потребностями.

2.5.2 Извлечение текста после n-го вхождения разделителя

Чтобы извлечь текст после n-го вхождения разделителя, вы можете применить функцию ПРАВИЛЬНО с функциями ПОДСТАВИТЬ, ДЛСТР и НАЙТИ.

Общая формула

=RIGHT(SUBSTITUTE(text_string, «delimiter», CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, «delimiter», CHAR(9), n), 1) + 1)

аргументы

Теперь вы можете применить эту формулу для извлечения текста после второго вхождения пробела из каждой ячейки в диапазоне B5:B10 следующим образом.

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

=RIGHT(SUBSTITUTE(B5, » «, CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, » «, CHAR(9), 2), 1) + 1)

2.6 Извлечение текста до или после разрыва строки

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

2.6.1 Извлечение текста до разрыва первой строки с формулой

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

Общая формула

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

аргументы

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

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Затем вы можете увидеть текст перед извлечением первого разрыва строки в каждой ячейке в диапазоне B5: B8, как показано на снимке экрана ниже.

Примечание: В формуле CHAR(10) представляет разрыв строки в Windows.

2.6.2 Извлечение текста после последнего разрыва строки с формулой

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

Общая формула

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(» «,200)),200))

аргументы

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

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(» «,200)),200))

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

Примечание: В формуле CHAR(10) представляет разрыв строки в Windows.

2.7 Извлечение текста до или после слова

В предыдущих частях мы научились извлекать текст до или после символа или разделителя. Что вы должны сделать, чтобы извлечь текст до или после всего слова? В этом разделе представлены три метода, которые помогут вам выполнить эту задачу.

2.7.1 Извлечь текст перед определенным словом с помощью формулы

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

Общая формула

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

аргументы

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

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

=IFERROR(LEFT(B5,FIND(«Excel»,B5)-1),B5)

Ноты:

2.7.2 Извлечь текст после определенного слова с помощью формулы

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

Общая формула

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

аргументы

Выберите ячейку, введите приведенную ниже формулу и нажмите кнопку Enter Ключ, чтобы получить результат. Выберите ячейку результата, а затем перетащите ее маркер автозаполнения вниз, чтобы применить эту формулу к другим ячейкам.

=TRIM(MID(B5,SEARCH(«Excel»,B5)+LEN(«Excel»),255))

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

Ноты:

2.7.3 Извлекайте текст до или после определенного слова с помощью замечательного инструмента

Если вы чувствуете, что использование смеси может вызвать много неудобств, здесь настоятельно рекомендуется Извлечь текст полезности Kutools for Excel. Эта функция помогает автоматизировать задачу извлечения в Excel всего за несколько кликов.

1. Нажмите Кутулс > Текст > Извлечь текст для включения этой функции.

2. в Извлечь текст диалоговом окне выполните следующие настройки.

Ноты: Если вы хотите создать динамические результаты, установите флажок Вставить как формулу коробка. Затем результаты будут автоматически обновляться при изменении данных в диапазоне.

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

Затем тексты до или после определенного слова в каждой ячейке в выбранном диапазоне извлекаются немедленно.

Примечание: Эта функция чувствительна к регистру.

Нажмите, чтобы узнать больше об этой функции.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

3. Извлечение между символами/словами

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

3.1 Извлечение текста между двумя символами

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

3.1.1 Извлечение текста между двумя одинаковыми символами с помощью формулы

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

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

=SUBSTITUTE(MID(SUBSTITUTE(«/» & B5&REPT(» «,6),»/»,REPT(«,»,255)),2*255,255),»,»,»»)

Затем текст между двумя одинаковыми символами «/» извлекается из каждой ячейки диапазона. Смотрите скриншот:

Примечание:

3.1.2 Извлечение текста между двумя разными символами с помощью формулы

Узнав, как извлекать текст между двумя одинаковыми символами в ячейке, здесь мы продемонстрируем формулу для извлечения текста между двумя разными символами. Как показано на снимке экрана ниже, чтобы извлечь только адрес электронной почты между «<» и «>» из каждой ячейки в столбце B, вы можете сделать следующее.

Общая формула

=MID(LEFT(cel,FIND(«end_char»,cell)-1),FIND(«start_char»,cell)+1,LEN(cell))

аргументы

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

=MID(LEFT(B5,FIND(«>»,B5)-1),FIND(«<«,B5)+1,LEN(B5))

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

3.1.3 Извлекайте текст между двумя символами с помощью замечательного инструмента

Здесь настоятельно рекомендуется Извлечь строки между указанным текстом особенность Kutools for Excel чтобы помочь вам легко извлечь текст между двумя одинаковыми или разными символами в ячейке Excel.

1. Выберите пустую ячейку для вывода результата, а затем щелкните Кутулс > Формула Помощник > Формула Помощник.

2. в Помощник по формулам диалоговом окне выполните следующие настройки.

3. Затем извлекается только текст между «<» и «>» в ​​ячейке B5. Между тем, формула была создана, вы можете выбрать эту ячейку результата, а затем перетащить ее дескриптор автозаполнения вниз, чтобы извлечь тексты из других ячеек в том же списке.

Нажмите, чтобы узнать больше об этой функции.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

3.1.4 Извлечение текста между двумя символами (включая символы) по правилу

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

1. Нажмите Кутулс > Текст > Извлечь текст.

2. в Извлечь текст диалоговом окне выполните следующие настройки.

3. Еще один Извлечь текст появится диалоговое окно, выберите ячейку для вывода результата, а затем нажмите кнопку OK кнопку.

Затем текст между указанными символами (включая символы) извлекается из каждой ячейки в выбранном диапазоне оптом.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

3.2 Извлечение текста между двумя словами

Помимо извлечения текста между двумя символами, вам также может понадобиться извлечь текст между двумя словами. Например, извлеките все текстовые строки между двумя словами «KTE» и «feature» из каждой ячейки в столбце B, как показано на снимке экрана ниже. Вы можете попробовать один из следующих способов, чтобы сделать это.

3.2.1 Извлечение текста между двумя словами с помощью формулы

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

Общая формула

=MID(cell,SEARCH(«start_word»,cell)+3,SEARCH(«end_word»,cell)-SEARCH(«start_word»,cell)-4)

аргументы

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

=MID(B5,SEARCH(«KTE»,B5)+3,SEARCH(«feature»,B5)-SEARCH(«KTE»,B5)-4)

Внимание: В формуле число 3 представляет длину символа слова «KTE»; число 4 представляет длину символа слова «KTE» плюс 1.

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

3.2.2 Извлекайте текст между двумя словами с помощью замечательного инструмента

Многим пользователям Excel может быть трудно запомнить формулы и работать с ними. Здесь, с Извлечь строки между указанным текстом особенность Kutools for Excel, вы можете легко извлечь текст между двумя словами всего за несколько кликов.

1. Выберите ячейку для вывода результата и нажмите Кутулс > Формула Помощник > Формула Помощник.

2. в Формула Помощник диалоговое окно необходимо настроить следующим образом.

3. Затем извлекаются все текстовые строки между двумя словами «KTE» и «feature» в ячейке B5. Между тем, формула была создана, вы можете выбрать эту ячейку результата, а затем перетащить ее дескриптор автозаполнения вниз, чтобы извлечь тексты из других ячеек в том же списке.

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Извлечь числа по позиции

Для списка буквенно-цифровых строк может быть три случая:

1. Номер стоит в начале текста;
2. Номер в конце текста;
3. Номер может быть где угодно в тексте.

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

1 Извлечь число слева от строки

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

Общая формула

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT(«1:»&LEN(cell)+1)), 1) *1), 0) -1)

аргументы

Примечание:

Выберите пустую ячейку, введите приведенную ниже формулу и нажмите Ctrl + Shift + Enter or Enter Ключ, чтобы получить результат. Выберите эту ячейку результата, а затем перетащите ее маркер автозаполнения вниз, чтобы получить номера других ячеек.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT(«1:»&LEN(B5)+1)), 1) *1), 0) -1)

Ноты:

2 Извлечь число справа от строки

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

Общая формула

=RIGHT(cell, LEN(cell) — MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT(«1:»&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT(«1:»&LEN(cell))), 0)))

аргументы

Примечание:

Выберите пустую ячейку, введите приведенную ниже формулу и нажмите Ctrl + Shift + Enter or Enter Ключ, чтобы получить результат. Выберите эту ячейку результата, а затем перетащите ее маркер автозаполнения вниз, чтобы получить номера других ячеек.

=RIGHT(B5, LEN(B5) — MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT(«1:»&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT(«1:»&LEN(B5))), 0)))

Ноты:

3. Извлечь все числа из любой позиции в текстовой строке.

Приведенные выше методы помогают извлечь число только слева или справа от текстовой строки. Если вы хотите извлечь все числа из любой точки текстовой строки, здесь мы предлагаем вам три способа сделать это.

3.1 Извлечь все числа из любой точки строки с помощью формулы

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

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

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(—MID(B5, ROW(INDIRECT(«1:»&LEN(B5))), 1)) * ROW(INDIRECT(«1:»&LEN(B5))), 0), ROW(INDIRECT(«1:»&LEN(B5))))+1, 1) * 10^ROW(INDIRECT(«1:»&LEN(B5)))/10)

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

3.2 Извлечение всех чисел из любой точки строки с помощью VBA

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

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. В дебюте Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль. Затем скопируйте приведенный ниже VBA в окно кода модуля.

Код VBA: извлечь все числа из любой точки текстовой строки

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

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

4. Затем еще один KutoolsforExcel всплывает диалоговое окно. В этом диалоговом окне выберите ячейку назначения и щелкните ОК.

Затем все числа извлекаются из каждой ячейки в выбранном диапазоне оптом.

4. Извлечение чисел после определенного текста

Как показано на снимке экрана ниже, чтобы извлечь любые числа после определенного текста «Нет», в этом разделе представлены два метода, которые помогут вам это сделать.

4.1 Извлечение чисел после определенного текста с помощью формулы

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

Общая формула:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&»0123456789″,FIND(«text»,» «&cell&» «))),{2,3,4,5,6}))

аргументы

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

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&»0123456789″,FIND(«No.»,» «&B5&» «))),{2,3,4,5,6}))

Ноты:

4.2 Извлечение чисел после определенного текста с помощью определяемой пользователем функции

Следующая определяемая пользователем функция также может помочь извлечь числа после определенного текста в ячейке. Пожалуйста, сделайте следующее.

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте приведенный ниже код VBA в окно кода модуля.

Код VBA: извлекать числа после определенного текста в ячейке

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

4. Выберите ячейку, введите приведенную ниже формулу и нажмите кнопку Enter ключ. Выберите эту ячейку результата, а затем перетащите ее маркер автозаполнения вниз, чтобы применить эту формулу к другим ячейкам.

=GetNumberAfterTheChar(B5,»No. «)

Ноты:

Статьи по теме:

Краткое руководство по текстовым функциям

Введение

Использование строк является очень важной частью VBA. Есть много типов манипуляций, которые вы можете делать со строками. К ним относятся такие задачи, как:

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

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

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

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

Прочитайте это в первую очередь!

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

Исходная строка не изменяется

Важно помнить, что строковые функции VBA не изменяют исходную строку. Они возвращают новую строку с изменениями, внесенными функцией. Если вы хотите изменить исходную строку, вы просто назначаете результат исходной строке. См. Раздел «Извлечение части строки» для примеров.

Как использовать Compare

Некоторые строковые функции, такие как StrComp (), Instr () и т.д. имеют необязательный параметр Compare. Он работает следующим образом:

vbTextCompare: верхний и нижний регистры считаются одинаковыми

vbBinaryCompare: верхний и нижний регистр считаются разными

Следующий код использует функцию сравнения строк StrComp () для демонстрации параметра Compare.

Sub Comp1()

    ' Печатает 0  : Строки совпадают
    Debug.Print StrComp("АБВ", "абв", vbTextCompare)
    ' Печатает -1 : Строки не совпадают
    Debug.Print StrComp("АБВ", "абв", vbBinaryCompare)

End Sub

Вы можете использовать параметр Option Compare вместо того, чтобы каждый раз использовать этот параметр. Опция сравнения устанавливается в верхней части модуля. Любая функция, которая использует параметр Compare, примет этот параметр по умолчанию. Два варианта использования Option Compare:

• Oпция Compare Text: делает vbTextCompare аргументом сравнения по умолчанию

Option Compare Text

Sub Comp2()
    ' Соответствие строк - использует vbCompareText в качестве 'аргумента сравнения
    Debug.Print StrComp("АБВ", "абв")
    Debug.Print StrComp("ГДЕ", "где")
End Sub

• Опция Compare Binary: делает vbBinaryCompare аргументом сравнения по умолчанию.

Option Compare Binary

Sub Comp2()
    ' Строки не совпадают - использует vbCompareBinary в качестве 'аргумента сравнения
    Debug.Print StrComp("АБВ", "абв")
    Debug.Print StrComp("ГДЕ", "где")
End Sub

Если Option Compare не используется, то по умолчанию используется Option Compare Binary.

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

Добавление строк

Вы можете добавлять строки, используя оператор &. Следующий код показывает несколько примеров его использования.

Sub Dobavlenie()

    Debug.Print "АБВ" & "ГДЕ"
    Debug.Print "Иван" & " " & "Петров"
    Debug.Print "Длинный " & 22
    Debug.Print "Двойной " & 14.99
    Debug.Print "Дата " & #12/12/2015#

End Sub

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

 Это даст сообщение об ошибке: «Несоответствие типов»
    Debug.Print "Длинный " + 22

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

Извлечение части строки

Функции, обсуждаемые в этом разделе, полезны при базовом извлечении из строки. Для чего-то более сложного можете посмотреть раздел, как легко извлечь любую строку без использования VBA InStr.

Функции Left, Right и Mid используются для извлечения частей строки. Это очень простые в использовании функции. Left читает символы слева, Right справа и Mid от указанной вами начальной точки.

Sub IspLeftRightMid()

    Dim sCustomer As String
    sCustomer = "Иван Васильевич Петров"

    Debug.Print Left(sCustomer, 4)  '  Печатает: Иван
    Debug.Print Right(sCustomer, 6) '  Печатает: Петров

    Debug.Print Left(sCustomer, 15)  '  Печатает: Иван Васильевич
    Debug.Print Right(sCustomer, 17)  '  Печатает: Васильевич Петров

    Debug.Print Mid(sCustomer, 1, 4) ' Печатает: Иван
    Debug.Print Mid(sCustomer, 6, 10) ' Печатает: Васильевич
    Debug.Print Mid(sCustomer, 17, 6) ' Печатает: Петров

End Sub

Как упоминалось в предыдущем разделе, строковые функции VBA не изменяют исходную строку. Вместо этого они возвращают результат в виде новой строки.

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

Sub PrimerIspolzovaniyaLeft()

    Dim Fullname As String
    Fullname = "Иван Петров"

    Debug.Print "Имя: "; Left(Fullname, 4)
    ' Исходная строка не изменилась
    Debug.Print "Полное имя: "; Fullname

 End Sub

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

Sub IzmenenieStroki()

    Dim name As String
    name = "Иван Петров"

    ' Присвойте возвращаемую строку переменной имени
    name = Left(name, 4)

    Debug.Print "Имя: "; name

 End Sub

Поиск в строке

InStr и InStrRev — это функции VBA, используемые для поиска текста в тексте. Если текст поиска найден, возвращается позиция (с начала строки проверки) текста поиска. Когда текст поиска не найден, возвращается ноль. Если какой-либо текст имеет значение null, возвращается значение null.

InStr Описание параметров

InStr() Start[Необязат], String1, String2, Compare[Необязат]

• Start [Необязательно — по умолчанию 1]: это число, указывающее начальную позицию поиска слева
• String1: текст, в котором будем искать
• String2: текст, который будем искать
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование InStr и примеры

InStr возвращает первую позицию в тексте, где найден данный текст. Ниже приведены некоторые примеры его использования.

Sub PoiskTeksta()

    Dim name As String
    name = "Иван Петров"

    ' Возвращает 3 - позицию от первой 
    Debug.Print InStr(name, "а")
    ' Возвращает 10 - позиция первого "а", начиная с позиции 4
    Debug.Print InStr(4, name, "а")
    ' Возвращает 8
    Debug.Print InStr(name, "тр")
    ' Возвращает 6
    Debug.Print InStr(name, "Петров")
    ' Возвращает 0 - текст "ССС" не найдет
    Debug.Print InStr(name, "ССС")

End Sub

InStrRev Описание параметров

InStrRev() StringCheck, StringMatch, Start[Необязат], Compare[Необязат]

• StringCheck: текст, в котором будем искать
• StringMatch: Текст, который будем искать
• Start [Необязательно — по умолчанию -1]: это число, указывающее начальную позицию поиска справа
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование InStrRev и примеры

Функция InStrRev такая же, как InStr, за исключением того, что она ищет с конца строки. Важно отметить, что возвращаемая позиция является позицией с самого начала. Поэтому, если существует только один экземпляр элемента поиска, InStr () и InStrRev () будут возвращать одно и то же значение.

В следующем коде показаны некоторые примеры использования InStrRev.

Sub IspInstrRev()

    Dim name As String
    name = "Иван Петров"

    ' Обе возвращают 1 - позицию, только И
    Debug.Print InStr(name, "И")
    Debug.Print InStrRev(name, "И")

    ' Возвращает 11 - вторую в
    Debug.Print InStrRev(name, "в")
    ' Возвращает 3 - первую в с позиции 9
    Debug.Print InStrRev(name, "в", 9)

    ' Returns 1
    Debug.Print InStrRev(name, "Иван")

End Sub

Функции InStr и InStrRev полезны при работе с базовым поиском текста. Однако, если вы собираетесь использовать их для извлечения текста из строки, они могут усложнить задачу. Я написал о гораздо лучшем способе сделать это в своей статье Как легко извлечь любой текст без использования VBA InStr.

Удаление пробелов

Функции Trim — это простые функции, которые удаляют пробелы в начале или конце строки.

Функции и примеры использования триммера Trim

• LTrim удаляет пробелы слева от строки
• RTrim удаляет пробелы справа от строки
• Trim удаляет пробелы слева и справа от строки

Sub TrimStr()

    Dim name As String
    name = "  Иван Петров  "

    ' Печатает "Иван Петров  "
    Debug.Print LTrim(name)
    ' Печатает "  Иван Петров"
    Debug.Print RTrim(name)
    ' Печатает "Иван Петров"
    Debug.Print Trim(name)

End Sub

Длина строки

Len — простая функция при использовании со строкой. Она просто возвращает количество символов, которое содержит строка. Если используется с числовым типом, таким как long, он вернет количество байтов.

Sub IspLen()

    Dim name As String
    name = "Иван Петров"

    ' Печатает 11
    Debug.Print Len("Иван Петров")
    ' Печатает 3
    Debug.Print Len("АБВ")

    ' Печатает 4 с Long - это размер 4 байта
    Dim total As Long
    Debug.Print Len(total)

End Sub

Перевернуть текст

StrReverse — еще одна простая в использовании функция. Он просто возвращает данную строку с обратными символами.

Sub RevStr()

    Dim s As String
    s = "Иван Петров"
    ' Печатает: вортеП навИ
    Debug.Print StrReverse(s)

End Sub

Сравнение

Функция StrComp используется для сравнения двух строк. Следующие подразделы описывают, как используется.

Описание параметров

StrComp()  String1, String2, Compare[Необязат]

• String1: первая строка для сравнения
• String2: вторая строка для сравнения
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

StrComp Возвращаемые значения

Использование и примеры

Ниже приведены некоторые примеры использования функции StrComp.

Sub IspStrComp()

   ' Возвращает  0
   Debug.Print StrComp("АБВ", "АБВ", vbTextCompare)
   ' Возвращает 1
   Debug.Print StrComp("АБВГ", "АБВ", vbTextCompare)
   ' Возвращает -1
   Debug.Print StrComp("АБВ", "АБВГ", vbTextCompare)
   ' Returns Null
   Debug.Print StrComp(Null, "АБВГ", vbTextCompare)

End Sub

Сравнение строк с использованием операторов

Вы также можете использовать знак равенства для сравнения строк. Разница между сравнением equals и функцией StrComp:

1. Знак равенства возвращает только true или false.
2. Вы не можете указать параметр Compare, используя знак равенства — он использует настройку «Option Compare».  

Ниже приведены некоторые примеры использования equals для сравнения строк.

Option Compare Text

Sub CompareIspEquals()

    ' Возвращает true
    Debug.Print "АБВ" = "АБВ"
    ' Возвращает true, потому что «Сравнить текст» установлен выше
    Debug.Print "АБВ" = "абв"
    ' Возвращает false
    Debug.Print "АБВГ" = "АБВ"
    ' Возвращает false
    Debug.Print "АБВ" = "АБВГ"
    ' Возвращает null
    Debug.Print Null = "АБВГ"

End Sub

Сравнение строк с использованием сопоставления с шаблоном

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

Если строка соответствует шаблону, возвращаемое значение равно true, в противном случае — false.

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

Давайте посмотрим на базовый пример с использованием знаков. Возьмите следующую строку шаблона.

[abc][!def]?#X*

 Давайте посмотрим, как работает эта строка

[abc] — символ, который является или a, b или c
[! def] — символ, который не является d, e или f
? любой символ
# — любая цифра
X — символ X
* следуют ноль или более символов

 Поэтому следующая строка действительна
apY6X

а — один из символов a,b,c
p — не один из символов d, e или f
Y — любой символ
6 — это цифра
Х — это буква Х

В следующих примерах кода показаны результаты различных строк с этим шаблоном.

Sub Shabloni()

    ' ИСТИНА
    Debug.Print 1; "apY6X" Like "[abc][!def]?#X*"
    ' ИСТИНА - любая комбинация символов после x действительна
    Debug.Print 2; "apY6Xsf34FAD" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - символ не из[abc]
    Debug.Print 3; "dpY6X" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - 2-й символ e находится в [def]
    Debug.Print 4; "aeY6X" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - A в позиции 4 не является цифрой
    Debug.Print 5; "apYAX" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - символ в позиции 5 должен быть X
    Debug.Print 1; "apY6Z" Like "[abc][!def]?#X*"

End Sub

Реальный пример сопоставления с образцом

Чтобы увидеть реальный пример использования сопоставления с образцом, ознакомьтесь с Примером 3: Проверьте, допустимо ли имя файла.

Важное примечание о сопоставлении с образцом VBA

Оператор Like использует двоичное или текстовое сравнение на основе параметра Option Compare. Пожалуйста, смотрите раздел Сравнение для более подробной информации.

Заменить часть строки

Replace используется для замены текста в строке другим текстом. Он заменяет все экземпляры текста, найденные по умолчанию.

Replace описание параметров

Replace()  Expression, Find, Replace, Start[Необязат], Count[Необязат], Compare[Необязат]

• Expression: текст, в котором нужна замена символов
• Find: текст для замены в строке выражения
• Replace: строка для поиска замены текста поиска
• Start [Необязательно — по умолчанию 1]: начальная позиция в строке
• Count [Необязательно — по умолчанию -1]: количество замен. По умолчанию -1 означает все.
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование и примеры

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

Sub PrimeriReplace()

    ' Заменяет все знаки вопроса (?) на точку с запятой (;)
    Debug.Print Replace("A?B?C?D?E", "?", ";")
    ' Заменить Петров на Иванов
    Debug.Print Replace("Евгений Петров,Артем Петров", "Петров", "Иванов")
    ' Заменить AX на AB
    Debug.Print Replace("ACD AXC BAX", "AX", "AB")

End Sub

На выходе:

A;B;C;D;E
Евгений Иванов,Артем Иванов
ACD ABC BAB

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

Sub ReplaceCount()

    ' Заменяет только первый знак вопроса
    Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=1)
    ' Заменяет первые три знака вопроса
    Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=3)

End Sub

На выходе:

A;B?C?D?E
A;B;C;D?E

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

Sub ReplacePartial()

    ' Использовать оригинальную строку из позиции 4
    Debug.Print Replace("A?B?C?D?E", "?", ";", Start:=4)
    ' Используйте оригинальную строку из позиции 8
    Debug.Print Replace("AA?B?C?D?E", "?", ";", Start:=8)
    ' Элемент не заменен, но по-прежнему возвращаются только последние '2 символа
    Debug.Print Replace("ABCD", "X", "Y", Start:=3)

End Sub

На выходе:

;C;D;E
;E
CD

Иногда вы можете заменить только заглавные или строчные буквы. Вы можете использовать параметр Compare для этого. Он используется во многих строковых функциях. Для получения дополнительной информации об этом проверьте раздел сравнения.

Sub ReplaceCase()

    ' Заменить только заглавные А
    Debug.Print Replace("AaAa", "A", "X", Compare:=vbBinaryCompare)
    ' Заменить все А
    Debug.Print Replace("AaAa", "A", "X", Compare:=vbTextCompare)

End Sub

На выходе:

XaXa
XXXX

Многократные замены

Если вы хотите заменить несколько значений в строке, вы можете вкладывать вызовы. В следующем коде мы хотим заменить X и Y на A и B соответственно.

Sub ReplaceMulti()

    Dim newString As String

    ' Заменить А на Х
    newString = Replace("ABCD ABDN", "A", "X")
    ' Теперь замените B на Y в новой строке
    newString = Replace(newString, "B", "Y")

    Debug.Print newString

End Sub

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

Sub ReplaceMultiNested()

    Dim newString As String

    ' Заменить A на X, а B на Y
    newString = Replace(Replace("ABCD ABDN", "A", "X"), "B", "Y")

    Debug.Print newString

End Sub

Результатом обоих этих Subs является:
XYCD XYDN

Преобразовать типы в строку (базовый)

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

Sub AutoConverts()

    Dim s As String
    ' Автоматически преобразует число в строку
    s = 12.99
    Debug.Print s

    ' Автоматически преобразует несколько чисел в строку
    s = "ABC" & 6 & 12.99
    Debug.Print s

    ' Автоматически преобразует двойную переменную в строку
    Dim d As Double, l As Long
    d = 19.99
    l = 55
    s = "Значения: " & d & " " & l
    Debug.Print s

End Sub

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

Явное преобразование

В некоторых случаях вы можете захотеть преобразовать элемент в строку без необходимости сначала помещать его в строковую переменную. В этом случае вы можете использовать функции Str или CStr. Оба принимают выражение как функцию, и это может быть любой тип, например long, double, data или boolean.

Давайте посмотрим на простой пример. Представьте, что вы читаете список значений из разных типов ячеек в коллекцию. Вы можете использовать функции Str / CStr, чтобы гарантировать, что они все хранятся в виде строк. Следующий код показывает пример этого:

Sub IspStr()

    Dim coll As New Collection
    Dim c As Range

    ' Считать значения ячеек в коллекцию
    For Each c In Range("A1:A10")
        ' Используйте Str для преобразования значения ячейки в строку
        coll.Add Str(c)
    Next

    ' Распечатайте значения и тип коллекции
    Dim i As Variant
    For Each i In coll
        Debug.Print i, TypeName(i)
    Next

End Sub

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

Multi Region

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

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

Преобразовать строку в число — CLng, CDbl, Val и т.д.

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

Sub StrToNumeric()

    Dim l As Long, d As Double, c As Currency
    Dim s As String
    s = "45.923239"

    l = s
    d = s
    c = s

    Debug.Print "Long is "; l
    Debug.Print "Double is "; d
    Debug.Print "Currency is "; c

End Sub

Использование типов преобразования дает большую гибкость. Это означает, что вы можете определить тип во время выполнения. В следующем коде мы устанавливаем тип на основе аргумента sType, передаваемого в функцию PrintValue. Поскольку этот тип может быть прочитан из внешнего источника, такого как ячейка, мы можем установить тип во время выполнения. Если мы объявим переменную как Long, то при выполнении кода она всегда будет длинной.

Sub Test()
    ' Печатает  46
    PrintValue "45.56", "Long"
    ' Печатает 45.56
    PrintValue "45.56", ""
End Sub

Sub PrintValue(ByVal s As String, ByVal sType As String)

    Dim value

    ' Установите тип данных на основе строки типа
    If sType = "Long" Then
        value = CLng(s)
    Else
        value = CDbl(s)
    End If
    Debug.Print "Type is "; TypeName(value); value

End Sub

Если строка не является допустимым числом (т.е. Содержит символы, другие цифры), вы получаете ошибку «Несоответствие типов».

Sub InvalidNumber()

    Dim l As Long

    ' Даст ошибку несоответствия типов
    l = CLng("45A")

End Sub

Функция Val

Функция преобразует числовые части строки в правильный тип числа.

Val преобразует первые встреченные числа. Как только он встречает буквы в строке, он останавливается. Если есть только буквы, то в качестве значения возвращается ноль. Следующий код показывает некоторые примеры использования Val

Sub IspVal()

    ' Печатает 45
    Debug.Print Val("45 Новая улица")

    ' Печатает 45
    Debug.Print Val("    45 Новая улица")

    ' Печатает 0
    Debug.Print Val("Новая улица 45")

    ' Печатает 12
    Debug.Print Val("12 f 34")

End Sub

Val имеет два недостатка

1. Не мультирегиональный — Val не распознает международные версии чисел, такие как запятые вместо десятичных. Поэтому вы должны использовать вышеуказанные функции преобразования, когда ваше приложение будет использоваться в нескольких регионах.
2. Преобразует недопустимые строки в ноль — в некоторых случаях это может быть нормально, но в большинстве случаев лучше, если неверная строка вызывает ошибку. Затем приложение осознает наличие проблемы и может действовать соответствующим образом. Функции преобразования, такие как CLng, вызовут ошибку, если строка содержит нечисловые символы.

Генерация строки элементов — функция строки

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

Sub IspString()

    ' Печатает: AAAAA
    Debug.Print String(5, "A")
    ' Печатает: >>>>>
    Debug.Print String(5, 62)
    ' Печатает: (((ABC)))
    Debug.Print String(3, "(") & "ABC" & String(3, ")")

End Sub

Преобразовать регистр / юникод — StrConv, UCase, LCase

Если вы хотите преобразовать регистр строки в верхний или нижний регистр, вы можете использовать функции UCase и LCase для верхнего и нижнего соответственно. Вы также можете использовать функцию StrConv с аргументом vbUpperCase или vbLowerCase. В следующем коде показан пример использования этих трех функций.

Sub ConvCase()

    Dim s As String
    s = "У Мэри был маленький ягненок"

    ' верхний
    Debug.Print UCase(s)
    Debug.Print StrConv(s, vbUpperCase)

    ' нижний
    Debug.Print LCase(s)
    Debug.Print StrConv(s, vbLowerCase)

    ' Устанавливает первую букву каждого слова в верхний регистр
    Debug.Print StrConv(s, vbProperCase)

End Sub

На выходе: 

У МЭРИ БЫЛ МАЛЕНЬКИЙ ЯГНЕНОК
У МЭРИ БЫЛ МАЛЕНЬКИЙ ЯГНЕНОК
у мэри был маленький ягненок
у мэри был маленький ягненок
У Мэри Был Маленький Ягненок

Другие преобразования

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

Использование строк с массивами

Строка в массив с использованием Split

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

Sub StrToArr()

    Dim arr() As String
    ' Разобрать строку в массив
    arr = Split("Иван,Анна,Павел,София", ",")

    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

На выходе:

Иван
Анна
Павел
София

Если вы хотите увидеть некоторые реальные примеры использования Split, вы найдете их в статье Как легко извлечь любую строку без использования VBA InStr.

Массив в строку, используя Join

Если вы хотите построить строку из массива, вы можете легко это сделать с помощью функции Join. По сути, это обратная функция Split. Следующий код предоставляет пример использования Join

Sub ArrToStr()

    Dim Arr(0 To 3) As String
    Arr(0) = "Иван"
    Arr(1) = "Анна"
    Arr(2) = "Павел"
    Arr(3) = "София"

    ' Построить строку из массива
    Dim sNames As String
    sNames = Join(Arr, ",")

    Debug.Print sNames

End Sub

На выходе:

Иван, Анна, Павел, София

Форматирование строки

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

Sub FormatDate()

    Dim s As String
    s = "31/12/2019 10:15:45"

    ' Печатает: 31 12 19
    Debug.Print Format(s, "DD MM YY")
    ' Печатает: Thu 31 Dec 2019
    Debug.Print Format(s, "DDD DD MMM YYYY")
    ' Печатает: Thursday 31 December 2019
    Debug.Print Format(s, "DDDD DD MMMM YYYY")
    ' Печатает: 10:15
    Debug.Print Format(s, "HH:MM")
    ' Печатает: 10:15:45 AM
    Debug.Print Format(s, "HH:MM:SS AM/PM")

End Sub

В следующих примерах представлены некоторые распространенные способы форматирования чисел.

Sub FormatNumbers()

    ' Печатает: 50.00%
    Debug.Print Format(0.5, "0.00%")
    ' Печатает: 023.45
    Debug.Print Format(23.45, "00#.00")
    ' Печатает: 23,000
    Debug.Print Format(23000, "##,000")
    ' Печатает: 023,000
    Debug.Print Format(23000, "0##,000")
    ' Печатает: $23.99
    Debug.Print Format(23.99, "$#0.00")

End Sub

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

Полезный совет по использованию формата

Быстрый способ выяснить используемое форматирование — использовать форматирование ячеек на листе Excel. Например, добавьте число в ячейку. Затем щелкните правой кнопкой мыши и отформатируйте ячейку так, как вам нужно. Если вы довольны форматом, выберите «Пользовательский» в списке категорий слева. При выборе этого вы можете увидеть строку формата в текстовом поле типа. Это формат строки, который вы можете использовать в VBA.

Заключение

Практически в любом типе программирования вы потратите много времени на манипулирование строками. В этой статье рассматриваются различные способы использования строк в VBA.

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

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