Удалить часть строки в vba excel

Обновлено: 04.07.2024

Для данных типа String существует только одна операция - конкатенация (объединение). Например, результатом операции конкатенации трех строковых значений «Петр» & " " & «Иванович» будет строка «Петр Иванович». Возможно также использование другого оператора для операции конкатенации, например: «десяти» + «тысячник». Разница между этими выражениями состоит в том, что в первом случае операндами могут быть значения любого типа (они просто будут преобразовываться в строковые), а во втором - оба операнда должны иметь тип String.

Для работы со строками существует большое количество функций (таблица. Функции работы со строками).

Таблица «Функции работы со строками»

Функция	Описание	Пример
Len(str)	Определяет длину строки	Из а=lеn("Персонажи") следует а=9
Left (<строка>, <длина>)	Выделяет из аргумента <строка> указанное количество символов слева	Left(" 1234string", 4) ="1234"
Right(<строка>, <длина>)	Выделяет из аргумента <строка> указанное количество символов справа	Right(" 1234string", 6) ="string"
Mid(<строка>, <старт> [, <длина>])	Выделяет из аргумента <строка> подстроку с указанным числом символов, начиная с позиции <старт>	Mid ("12345678", 4, 3) ="456"
Mid(<строка>, <старт>)	Выделяется подстрока от позиции <старт> до конца строки	Mid ("12345678", 4) ="45678"
LTrim (<строка>)	Удаляет пробелы в начале строки	LTrim(" печать") ="печать"
RTrim (<строка>)	Удаляет пробелы в конце строки	RTrim("печать ") ="печать"
Trim (<строка>)	Удаляет пробелы в начале и в конце строки	Trim(" печать ") ="печать"
InStr([<старт>, ] < строка 1>, <строка 2> [, <сравнение>])	Производит поиск подстроки в строке. Возвращает позицию первого вхождения строки <строка 2> в строку <строка 1>, <старт> - позиция, с которой начинается поиск. Если этот аргумент пропущен, поиск начинается с начала строки	Instr("C:Temp test.mdb", "Test")=9 Если искомая строка не находится в указанной строке, функция возвращает 0
InStrRev ([<старт>, ] <строка 1>, <строка 2> [, <сравнение>])	Ищет подстроку в строке, но начинает поиск с конца строки и возвращает позицию последнего вхождения подстроки. Необязательный аргумент <сравнение> определяет тип сравнения двух строк
Replace (<строка>, <строка Поиск>, <строка Замена>)	Позволяет заменить в строке одну подстроку другой. Эта функция ищет все вхождения аргумента <строка Поиск> в аргументе <строка> и заменяет их на <строка Замена>

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

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

Split (<строка> [, <разделитель>]) - преобразует строку в массив подстрок. По умолчанию в качестве разделителя используется пробел. Данную функцию удобно использовать для разбиения предложения на слова. Однако можно указать в этой функции любой другой разделитель. Например, Split(3, «Это тестовое предложение») возвращает массив из трех строковых значений: «Это», «тестовое», «предложение».
Join (<массив Строк> [, <разделитель>]) - преобразует массив строк в одну строку с указанным разделителем.
Filter (<массив Строк>, <строка Поиск>[, <включение>] [, <сравнение>]) - просматривает массив строковых значений и ищет в нем все подстроки, совпадающие с заданной строкой.

Эта функция имеет четыре аргумента:

<строка Поиск> - искомая строка;
<включение> - параметр (boolean значение), который указывает, будут ли возвращаемые строки включать искомую подстроку или, наоборот, возвращаться будут только те строки массива, которые не содержат искомой строки в качестве подстроки;
<сравнение> - параметр, определяющий метод сравнения строк.

Еще три функции обеспечивают преобразование строк:

LCase (<строка>) - преобразует все символы строки к нижнему регистру, например функция LCase(«ПОЧTA») возвращает строку «почта»;
UCase (<строка>) - преобразует все символы строки к верхнему регистру;
StrConv (<строка>, <преобразование>) - выполняет несколько типов преобразований строки в зависимости от второго параметра. Этот параметр описывается встроенными константами, например функция StrConv(«poccия», VbProperCase) возвращает значение «Россия».

И последние две функции генерируют строки символов

Space (<число>) - создает строку, состоящую из указанного числа пробелов;
String (<число>, <символ>) - создает строку, состоящую из указанного в первом аргументе числа символов. Сам символ указывается во втором аргументе.

Пример

1 метка: сообщается длина строки, введенной в первое текстовое поле (1 строка);

2 метка: преобразует все символы третьего текстового поля (3 строка) в заглавные буквы;

3 метка: выводит вместе содержание первого и второго текстовых полей (1 и 2 строки).

Хороший фреймворк (framework) лучшее решение для безпроблемной работы со строками.

В этой статье разберем работу со строками в VBA на примерах функций InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse.

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

Синтаксис

Примеры

Строковые функции

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

Название функции и описание

InStr

Функция InStr возвращает первое вхождение одной строки в другую строку. Поиск происходит слева направо.

Синтаксис

Параметр Описание

Пуск - необязательный параметр. Указывает начальную позицию для поиска. Поиск начинается с первой позиции слева направо.
String1 - требуемый параметр. Строка для поиска.
String2 - требуемый параметр. Строка, по которой выполняется поиск String1.
Compare - Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
0 = vbBinaryCompare - выполняет двоичное сравнение (по умолчанию)
1 = vbTextCompare - выполняет сравнение текста

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11

Возвращает первое вхождение указанной подстроки. Поиск происходит слева направо.

Функция InStrRev возвращает первое вхождение одной строки в другую строку. Поиск происходит справа налево.

Синтаксис

Параметр Описание

String1 - требуемый параметр. Строка для поиска.
String2 - требуемый параметр. Строка, по которой выполняется поиск String1.
Пуск - необязательный параметр. Указывает начальную позицию для поиска. Поиск начинается с первой позиции справа налево.
Compare - Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
0 = vbBinaryCompare - выполняет двоичное сравнение (по умолчанию)
1 = vbTextCompare - выполняет сравнение текста

пример

Добавьте кнопку и установите следующую функцию.

После выполнения вышеуказанного скрипта он производит следующий результат.

Line 1 : 6
Line 2 : 6
Line 3 : 8
Line 4 : 0
Line 5 : 2
Line 6 : 2
Line 7 : 0

Возвращает первое вхождение указанной подстроки. Поиск происходит справа налево.

LCASE

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

Синтаксис

пример

Добавьте кнопку и поместите следующую функцию внутри нее.

После выполнения вышеуказанного скрипта он производит следующий вывод.

Line 1 : microsoft vbscript
Line 2 : ms vbscript
Line 3 : microsoft

Возвращает нижний регистр указанной строки.

UCase

Функция UCase возвращает строку после преобразования введенной строки в буквы буквы UPPER.

Синтаксис

пример

Добавьте кнопку и поместите следующую функцию внутри нее.

После выполнения вышеуказанного скрипта он производит следующий вывод.

Line 1 : MICROSOFT VBSCRIPT
Line 2 : MS VBSCRIPT
Line 3 : MICROSOFT

Возвращает верхний регистр указанной строки.

Функция Left возвращает указанное количество символов с левой стороны данной входной строки.

Синтаксис

Параметр Описание

String - обязательный параметр. Строка ввода, из которой указанное число символов должно быть возвращено с левой стороны.
Длина - требуемый параметр. Целое число, определяющее количество возвращаемых символов.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : Mi
Line 2 : MS VB
Line 3 : microsoft

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

Right

Функция Right возвращает указанное количество символов с правой стороны данной входной строки.

Синтаксис

Параметр Описание

String - обязательный параметр. Строка ввода, из которой указанное число символов должно быть возвращено с правой стороны.
Длина - требуемый параметр. Целое число, которое задает количество возвращаемых символов.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : pt
Line 2 : CRIPT
Line 3 : microsoft

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

Mid функция возвращает указанное количество символов из заданной входной строки.

Синтаксис

Параметр Описание

String - обязательный параметр. Строка ввода, из которой задано количество символов, которые нужно вернуть.
Начало - требуемый параметр. Целое число, определяющее начальную позицию строки.
Длина - необязательный параметр. Целое число, определяющее количество возвращаемых символов.

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V

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

LTrim

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

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

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

After Ltrim : Microsoft VBScript

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

RTrim

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

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

After Rtrim : Microsoft VBScript

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

Функция Trim удаляет как ведущее, так и конечное пустое пространство данной входной строки.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

After trim : Microsoft VBScript

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

Функция Len возвращает длину данной входной строки, включая пробелы.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Length of var1 is : 18
Length of var2 is : 36

Возвращает длину данной строки.

Replace

Функция Replace заменяет указанную часть строки на определенную строку, указанное количество раз.

Синтаксис

Параметр Описание

String - обязательный параметр. Строка ввода, которую нужно искать для замены.
Find - требуемый параметр. Часть строки, которая будет заменена.
Replacewith - обязательный параметр. Строка замены, которая будет заменена на параметр find.
Start - необязательный параметр. Задает начальную позицию, из которой нужно искать и заменять строку. Значение по умолчанию - 1.
Count - необязательный параметр. Указывает количество раз, которое должна выполняться замена.
Compare - Необязательный параметр. Указывает метод сравнения, который будет использоваться. Значение по умолчанию - 0.
0 = vbBinaryCompare - выполняет двоичное сравнение
1 = vbTextCompare - выполняет текстовое сравнение

пример

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Возвращает строку после замены строки другой строкой.

Space

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

Синтаксис

Параметр Описание

Номер - требуемый параметр. Количество пробелов, которые мы хотим добавить к данной строке.

пример

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

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

StrComp

Функция StrComp возвращает целочисленное значение после сравнения двух заданных строк. Он может возвращать любое из трех значений -1, 0 или 1 на основе входных строк для сравнения.

Если String1 меньше String2, то StrComp возвращает -1
Если String1 равно String2, то StrComp возвращает 0
Если String1 больше String2, то StrComp возвращает 1

Синтаксис

Параметр Описание

String1 - требуемый параметр. Первое строковое выражение.
String2 - требуемый параметр. Второе строковое выражение.
Compare - Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
0 = vbBinaryCompare - выполняет двоичное сравнение (по умолчанию)
1 = vbTextCompare - выполняет сравнение текста

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 :0
Line 2 :1
Line 3 :1
Line 4 :0
Line 5 :1

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

String

Функция String заполняет строку указанным символом для указанного количества раз.

Синтаксис

Параметр Описание

Номер - требуемый параметр. Целочисленное значение, которое будет повторяться в течение определенного количества раз против параметра символа.
Символ - требуемый параметр. Значение символа, которое должно повторяться определенное количество раз.

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 :$$$
Line 2 :****
Line 3 :ddddd
Line 4 :AAAAAA

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

StrReverse

Функция StrReverse меняет указанную строку.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Line 1 : TPIRCSBV
Line 2 : tpircSBV tsriF yM
Line 3 : 54.321

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

в следующей таблице перечислены функции, которые Visual Basic предоставляет в Microsoft.VisualBasic.Strings классе для поиска и работы со строками. их можно рассматривать как Visual Basic встроенных функций; то есть вам не нужно вызывать их как явные члены класса, как показано в примерах. Дополнительные методы и в некоторых случаях дополняют методы, доступны в System.String классе.

Можно использовать инструкцию Option Compare , чтобы задать, сравниваются ли строки с использованием порядка сортировки текста без учета регистра, определенного языковым стандартом системы ( Text ) или внутренними двоичными представлениями символов ( Binary ). Метод сравнения текста по умолчанию — Binary .

Пример: Укасе

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

Пример: LTrim

В данном примере функция LTrim используется, чтобы убрать пробелы в начале, а функция RTrim — чтобы убрать пробелы в конце строковой переменной. Функция Trim в примере используется для удаления обоих типов пробелов.

Пример: mid

В этом примере Mid функция используется для возврата указанного числа символов из строки.

Пример: len

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

Пример: InStr

В данном примере функция InStr используется для возврата позиции первого вхождения одной строки в другую.

Пример: Format

В данном примере показаны различные способы использования функции Format для форматирования значений с применением как форматов String , так и определенных пользователем форматов. Фактическое отображение системой разделителя даты ( / ), разделителя времени ( : и индикаторов AM/PM ( t и tt ) зависит от региональных параметров, применяемых кодом. При отображении времени и даты в среде разработки используется короткий формат времени и даты региональных установок кода.

Для языков, использующих 24-часовой формат, индикаторы AM/PM ( t и tt ) не отображаются.

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

Давайте рассмотрим несколько способов решения этой задачи.

Способ 1. Поиск пустых ячеек

Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.

Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):

Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:

Выделяем диапазон с городами (B2:B26)
Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special) .
В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl + минус - и наша задача решена.

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

Способ 2. Поиск незаполненных строк

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

Взгляните, например, на следующую таблицу - как раз такой случай:

Здесь подход будет чуть похитрее:

К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.

Способ 3. Макрос удаления всех пустых строк и столбцов на листе

Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .

В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:

Закройте редактор и вернитесь в Excel.

Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.

Способ 4. Запрос Power Query

Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.

Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:

Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:

Дальше всё просто:

Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:

Читайте также: