Подстрока в excel — как использовать функцию подстроки — слева, справа и в середине?

How to find substring in Excel

In situations when you don’t want to extract a substring and only want to find cells containing it, you use the SEARCH or FIND function as shown in the above examples, but perform the search within the ISNUMBER function. If a cell contains the substring, the Search function returns the position of the first character, and as long as ISNUMBER gets any number, it returns TRUE. If the substring is not found, the search results in an error, forcing ISNUMBER to return FALSE.

Supposing, you have a list of British postcodes in column A and you want to find those that contain the substring «1ZZ». To have it done, use this formula:

The results will look something similar to this:

If you’d like to return your own message instead of the logical values of TRUE and FALSE, nest the above formula into the IF function:

If a cell contains the substring, the formula returns «Yes», an empty string («») otherwise:

As you may remember, the Excel SEARCH function is case-insensitive, so you use it when the character case does not matter. To get your formula to distinguish the uppercase and lowercase characters, opt for the case-sensitive FIND function.

VBA Case Functions

The VBA LCase String Function

The VBA LCase Function converts letters in a text or string to lower case. The syntax of the VBA LCase String Function is:

LCase(String) where:

String – The original text.

The following code shows you how to use the LCase String Function to convert all the letters in the given string to lower case:

The result is:

The LCase Function has converted all the letters in the string to lower case.

The VBA UCase String Function

The VBA UCase Function converts letters in a text or string to upper case. The syntax of the VBA UCase String Function is:

UCase(String) where:

String – The original text.

The following code shows you how to use the UCase String Function to convert all the letters in the given string to upper case:

The result is:

The UCase Function has converted all the letters in the string to upper case.

The VBA StrConv Function

The VBA StrConv Function can convert letters in a text or string to upper case, lower case, proper case or unicode depending on type of conversion you specify.  The syntax of the VBA StrConv String Function is:

StrConv(String, Conversion, ) where:

• String – The original text.
• Conversion – The type of conversion that you want.
• (Optional) – An optional parameter that specifies the LocaleID. If blank, the system LocaleID is used.

The following code shows you how to use the StrConv String Function to convert the string to proper case:

The result is:

You specify the type of conversion you want to perform using the conversion parameter:

• vbLowerCase converts all the letters in the text to lower case.
• vbUpperCase converts all the letters in the text to upper case.
• vbProperCase converts the first letter of each word in the text to upper case, while all the other letters are kept as lower case.
• vbUnicode converts a string to unicode.
• vbFromUnicode converts a string from unicode to the default code page of the system.

Как найти несколько слов в Excel

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

Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:

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

Применяем фильтр

Итак, для того, чтобы воспользоваться фильтром, необходимо:

1. Выделить определенную ячейку, содержащую данные.
2. Кликнуть по главной, затем – «Сортировка», и далее – «Фильтр».
3. В строчке вверху клетки будут оснащены стрелочками. Это и есть меню, которое нужно открыть.
4. В текстовом поле нужно пропечатать запрос и нажать подтверждение.
5. В столбике будут отображаться лишь ячейки. В которых присутствует искомая фраза.
6. Для сброса результатов, в выпавшем списке следует отметить «Выделить все».
7. Для отключения фильтра, заново стоит нажать по нему в сортировке.

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

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

Cинтаксис.

Excel подстрока из строки

Описание

• ​ регистру.​ лишнего пробела в​ функцию​Скопируйте образец данных из​
• ​ПОИСКБ​Эта функция возвращает​ строках попадаются​ использовать функцию ПРАВСИМВ(),​ извлечь число или​ возвращает строку «»​
• ​В этой статье описаны​​В соседней ячейке​

• ​ Выделяет из текста​ ее в соседнюю​ принимающий целые числа​
• ​Для заполнения столбца «Наименование»​ и возвращает требуемое​Заменяет определенный текст или​ конце или начале​СЦЕПИТЬ​ следующей таблицы и​не учитывают регистр.​4​английские буквы​

• ​ но сколько символов​ дату из текстовой​ (пустую строку).​ синтаксис формулы и​ надо получить three​ субстринг/и, ориентируясь по​
• ​ ячейку справа.​ из диапазона от​ используем следующую формулу:​ количество символов начиная​ символ на требуемое​ строки сложно отследить,​. Она последовательно объединяет​ вставьте их в​ Если требуется учитывать​, так как «н»​. Их также можно​ извлечь? Два? А​

​ строки.​Если значение «начальная_позиция» меньше,​ использование функций​Формула, которая это​ символам-разделителям​

Синтаксис

1. ​китин​
2. ​ 1 до N,​
3. ​=ПСТР(A2;1;НАЙТИ(» «;A2))​ с заданной позиции.​

• ​ значение. В Excel​​ данная функция становится​ значения указанных ячеек​ ячейку A1 нового​
• ​ регистр, используйте функции​​ является четвертым символом​ обнаружить и извлечь,​ если в других​Данная статья является сводной,​ чем длина текста,​ПСТР​
• ​ делает (я проверял!)​​’ Notes :​: как то так​ где N –​
• ​Функция НАЙТИ возвращает номер​​Один символ в языках​ текстовую функцию​ просто незаменимой. На​ в одной строке.​

Замечания

• ​ листа Excel. Чтобы​НАЙТИ​ в слове «принтер».​ см. Есть ли в​ адресах номер дома​
• ​ т.е. в ней​ но сумма значений​и​ такова:​ Substring(текст; символ_разделитель; Начальный_Номер_фрагмента,​ формула массива​ длина строки, из​ позиции символа пробела​
• ​ с однобайтовой кодировкой​ПОДСТАВИТЬ​ рисунке ниже видно,​Если в Excel необходимо​
• ​ отобразить результаты формул,​и​Можно также находить слова​ слове в MS​
• ​ состоит из 1​ содержатся ссылки на​ «начальная_позиция» и «число_знаков»​ПСТРБ​

Пример

​СЖПРОБЕЛЫ(ЛЕВСИМВ(ПРАВСИМВ(ПОДСТАВИТЬ(D2,»|»,ПОВТОР(» «,99)),198),99))​ Конечный_Номер_фрагмента), где​200?’200px’:»+(this.

scrollHeight+5)+’px’);»>=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);ПОИСКПОЗ(1=1;ЕЧИСЛО(—ПСТР(ПОДСТАВИТЬ(A2;» «;»»);СТРОКА($1:$50);10));0);10)​ которой требуется извлечь​ « » в​ соответствует 1 байту.

​применяют, когда заранее​ что содержимое ячеек​ сделать все буквы​ выделите их и​НАЙТИБ​ в других словах.​ EXCEL латинские буквы,​

​ или 3 цифр?​
​ другие статьи, в​
​ превышают длину текста,​	​в Microsoft Excel.​	​Вопрос: откуда там​
​’ текст -​	​китин​ подстроку заданного размера.​ просматриваемой строке, который​ При работе с​	​ известно какой текст​
​ А1 и B1​	​ строчными, т.е. преобразовать​ нажмите клавишу F2,​.​ Например, функция​ цифры, ПРОПИСНЫЕ символы.​ В этом случае​ которых решены определенные​ функция ПСТР возвращает​Функция ПСТР возвращает заданное​ цифры 99, 198,​ текст, который делим​:​	​ Начальная позиция символа​
​ принимается в качестве​	​ такими языками результаты​ необходимо заменить, а​ абсолютно одинаково, но​ их в нижний​

support.office.com

VBA Excel. Функция Split (синтаксис, параметры, значения)

Использование функции Split в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией Split. Примеры использования.

Функция Split предназначена в VBA Excel для разделения строки на подстроки по специальным меткам — разделителям. Разделителем может быть как отдельный символ, так и строка из нескольких символов. Функция Split по своему действию является обратной функции Join, которая создает одну строку из массива подстрок.

1. Синтаксис функции
2. Параметры функции
3. Возвращаемые значения

Синтаксис функции

Split (Expression,,,)

Обязательным параметром функции Split является Expression — если остальные параметры явно не указаны, используются их значения по-умолчанию.

Параметры функции

Параметр	Описание	Значение по умолчанию
Expression	Строка, содержащая подстроки и разделители	Нет
Delimiter	Разделитель, представляющий один или более символов	Пробел
Limit	Максимальное число подстрок, на которые должна быть разделена входная строка	-1
Compare*	Определяет, какое используется сравнение, двоичное — CompareMethod.Binary (0) или текстовое — CompareMethod.Text (1)

*Если используется двоичное сравнение (0 или CompareMethod.Binary), функция чувствительна к регистру букв. Если используется текстовое сравнение (1 или CompareMethod.Text), функция не чувствительна к регистру букв.

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

Функция Split возвращает одномерный массив с индексацией от нуля, который содержит указанное параметром Limit число подстрок. Чаще всего, функция Split используется со значением параметра Limit по-умолчанию, равному -1, когда возвращаются все найденные в исходной строке подстроки.

Пример 1

Sub Test1()
Dim a() As String
a = Split(«vremya ne zhdet»)
MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)
End Sub

Результат в MsgBox: vremya ne zhdet

В первом примере используются Delimiter и Limit по-умолчанию.

Пример 2

Sub Test2()
Dim a() As String
a = Split(«vremya-ne-zhdet»,»-«, 2)
MsgBox a(0) & vbNewLine & a(1)
End Sub

Результат в MsgBox: vremya ne-zhdet

Во втором примере Delimiter = «-«, а Limit = 2.

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

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

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

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

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

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

Формула Excel: разделение текста и чисел

Как разделить или разбить строку в Python: всевозможные способы и примеры ()

1. Используйте формулу «=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)», чтобы отделить имя;
2. Используйте «=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))», чтобы найти отчество (в записи вроде «Иванов Иван Иванович»)
3. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))», чтобы извлечь фамилию.

Иная возможность состоит в том, что вы создаете константу со значением 2^31-1 (=2147483647), которое является наибольшим целочисленным значением со знаком в 32-разрядных системах, и даете ей прекрасное имя, к примеру MaxInt, тогда ваша 1-ая формула тоже будет действенной и прекрасной:

Как работает функция поиск в excel

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

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

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

Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

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

Синтаксис

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

Искомый_текст Обязательный. Текст, который требуется найти.

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

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

Замечание

Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если значение find_text не найдено, #VALUE! возвращается значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

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

Функция ПСТР (MID) — подробное описание

Итак, мы поняли, что ПСТР – это функция. которая используется для того, чтобы достать из одного фрагмента текста какой-то определенный. Но она несколько отличается от функции «Найти и заменить», которая может реализовываться как через меню Excel, так и посредством формул. Она возвращает строку, начинающуюся с определенного символа.

Синтаксис предельно прост:

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

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

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

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

Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов. 

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

=ПСТР(A1;17;10)

How to extract text from cell with Ultimate Suite for Excel

As you have just seen, Microsoft Excel provides an array of different functions to work with text strings. In case you are unsure which function is best suited for your needs, commit the job to our Ultimate Suite for Excel. With these tools in your Excel’s arsenal, you just go to Ablebits Data tab > Text group, and click Extract:

Now, you select the source cells, and whatever complex strings they contain, a substring extraction boils down to these two simple actions:

1. Specify how many characters you want to get from the start, end or middle of the string; or choose to extract all text before or after a given character.
2. Click Insert Results. Done!

And you will get the following results in a moment:

Apart from speed and simplicity, the Extract Text tool has extra value — it will help you learn Excel formulas in general and substring functions in particular. How? By selecting the Insert as formula checkbox at the bottom of the pane, you ensure that the results are output as formulas, not values.

In this example, if you select cells B2 and C2, you will see the following formulas, respectively:

• To extract username:

• To extract domain:

How much time would it take you to figure out these formulas on your own?

Since the results are formulas, the extracted substrings will update automatically as soon as any changes are made to the original strings. When new entries are added to your data set, you can copy the formulas to other cells as usual, without having to run the Extract Text tool anew.

Comparing Strings

The VBA StrComp Function

The VBA StrComp String Function allows you to compare two strings. The function returns:

• 0 if the two strings match
• -1 if string1 is less than string2
• 1 if string1 is greater than string2
• A null value if either of the strings was Null

The following code shows you how to use the StrComp Function to compare two strings:

The result is:

The StrComp Function has found an exact match between the two strings and returned 0.

The VBA Like Operator

The VBA Like Operator allows you to compare a text or string to a pattern and see if there is a match. You would usually use the Like Operator in conjunction with wildcards. The following code shows you how to use the Like Operator:

The result is:

The wildcards you can use with the Like Operator to find pattern matches include:

• ? which matches a single character
• # which matches a single digit
• * which matches zero or more characters

The following code shows you how you would use the Like Operator and the ? wildcard to match a pattern in your code:

The result delivered is:

ПСТР в Excel (функция получения подстроки)

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

ПСТР в Excel относится к одной из самых нередко применяемых функций, так как при обработке огромных массивов данных, импортированных из остальных программ, задачка по обрезке строк возникает достаточно нередко. Формула дозволяет получить часть строчки, задав начало и конец обрезки.

Поглядим применение функции ПСТР в Excel на практике, но до этого всего разберёмся с чертами данной формулы.

Функция VBA LEN

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

Пример

Фильтр

Вот как искать в Экселе, используя фильтр:

1. Выделите какую-нибудь заполненную ячейку.
2. Нажмите Главная — Сортировка — Фильтр.
3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
4. В текстовом поле введите запрос и нажмите «OK».
5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
6. Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

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

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

Использование текста в столбцы для извлечения подстроки в Excel

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

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

Вот как это сделать:

• Выделите ячейки, в которых есть текст.
• Перейдите в Data -> Data Tools -> Text to Columns.
• На шаге 1 мастера преобразования текста в столбец выберите «С разделителями» и нажмите «Далее».
• На шаге 2 установите флажок «Другое» и введите @ в поле справа от него. Это будет наш разделитель, который Excel будет использовать для разделения текста на подстроки. Вы можете увидеть предварительный просмотр данных ниже. Щелкните Далее.
• В шаге 3 в этом случае работают общие настройки. Однако вы можете выбрать другой формат, если вы разделяете числа / даты. По умолчанию в целевой ячейке находятся исходные данные. Если вы хотите сохранить исходные данные нетронутыми, измените это на другую ячейку.
• Щелкните Готово.

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

Если вы хотите дополнительно разделить текст (например, разделить batman.com на batman и com), повторите с ним тот же процесс.