Как в excel сделать разделение строки на подстроки?

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

Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных.

В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

• БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
• ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
• ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
• ЗНАЧЕН (Текст) – преобразует текст в число;
• ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
• ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
• НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
• ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
• ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
• СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1Дан набор строк:

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

    Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

=ПСТР(A2;НАЙТИ(«№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

• =ПРАВСИМВ(A2;8)
• но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:
• =ЗНАЧЕН(ПРАВСИМВ(A2;8))
• а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

=ЗНАЧЕН(СЦЕПИТЬ(«01»;ПРАВСИМВ(A2;6))) или =ЗНАЧЕН(«01″&ПРАВСИМВ(A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

1. Пример 2   В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».
2. Формула будет следующая:
3. =ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК,  последней будет ПОДСТАВИТЬ.

Прикрепленный файл: text_func_excel.zip

Источник: https://www.programm-school.ru/tekstovie_funkcii_excel.html

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

​Смотрите также​ | 0011​' Purpose :​​ цифр и вставить​​начальная_позиция – обязательный аргумент,​​Вид таблицы данных:​​ качестве первого аргумента,​

Описание

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

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

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

Источник: https://my-excel.ru/tekst/excel-podstroka-iz-stroki.html

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

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

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

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

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

Split (Expression,[Delimiter],[Limit],[Compare])

Обязательным параметром функции 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.

Источник: https://vremya-ne-zhdet.ru/vba-excel/funktsiya-split-parametry/

Разбор текстовых строк в 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 число из середины текстовой строки.

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

Про разбор фамилии см. Разделяем пробелами Фамилию, Имя и Отчество.

Источник: https://excel2.ru/articles/razbor-tekstovyh-strok-v-ms-excel

как разбить строку на подстроку в excel?

0.45::rafas::4.0::0.0::0.9 0.35::rasaf::4.0::110.0::1.0

и я хотел бы разбить строку на следующий (:: as separator) в Excel

col1 col2 col3 col4 col5 0.45::rafas::4.0::0.0::0.9 0.45 rafas 4.0 0.0 0.9 0.35::rasaf::4.0::110.0::1.0 0.35 rasaf 4.0 110 1.0

На этой странице объясняется, как это сделать, используя функцию «Текст в столбцы».

Скопировано для вашего удобства:

Выделите все ваши ячейки данными.

Выберите самую верхнюю ячейку в столбце, EG A1 Удерживайте CTRL + SHIFT, а затем нажмите стрелку вниз. Хорошо, как только мы это сделали, перейдите в меню «Данные» и выберите «Текст в столбцы». В окне «Текст в столбцы» выберите «Разграничено», а затем нажмите «Далее». В следующем окне выберите «Другое» для типа разделителя и используйте знак «минус / герб» – нажмите «Готово».

• Теперь у вас будет два столбца, из вашего примера первый столбец будет содержать данные типа «Животные», а в другом столбце будут содержаться данные «Дом восходящего солнца». (обратите внимание на ПРОСТРАНСТВО перед «Домом»)
• Чтобы избавиться от этого ПРОСТРАНСТВА, мы будем использовать функцию TRIM.
• В ячейке C1 (или в столбце справа от названий песен) введите эту формулу.
• = TRIM (В1)

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

Если вы хотите сделать это с помощью forumlae, а не с функциями «text to columns», вы можете использовать:

Предполагая строку в A1

in B1: =FIND(«::»,$A1) in C1: =FIND(«::»,$A1,B1+1)

Затем скопируйте C1 над D1: E1

in F1: =MID($A1,1,B1-1) in G1: =MID($A1,B1+2,C1-B1-2)

Затем скопируйте G1 над H1: I1 И, наконец,

in J1: =MID($A1,E1+2,LEN($A1)-E1-1)

Результаты раскола будут в F1: J1. Вы всегда можете скрыть столбцы B: E, поскольку они просто являются внутренними для разделения. Затем это можно сделать на столько строк, сколько вам нужно, и если значение в A1 будет обновлено, все остальные значения будут изменены. Тем не менее, он находится на фиксированном числе столбцов, но при необходимости может быть легко расширен.

Excel (и OpenOffice) имеют функцию разделения текста на столбцы. Выделите все столбцы, соответствующие этой схеме, затем перейдите в меню «Данные» и выберите «Текст в столбцы». Используется разделитель с разделителями и указывается как «:» при одновременном рассмотрении последовательных разделителей.

1. Вот очень простой способ извлечь пятый символ слева из текстовой строки в Excel:
2. Предположим, что символьная строка ABCDEFGHIJ хранится в ячейке A1 в электронной таблице Excel, затем следующая формула
3. = Вправо (влево (± 1,5), 1)
4. выдает 5-й символ слева в строке, а именно «E».

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

Function SplitTextToNum(rngInput As Range, sepString As String) Dim CallerRows As Long, CallerCols As Long, DimLimit As Long Dim outvar As Variant outvar = Split(rngInput.Value, sepString, -1, vbBinaryCompare) If Application.Caller.Rows.Count > 1 Then SplitTextToNum = Application.Transpose(outvar) Else SplitTextToNum = outvar End If End Function

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

Источник: https://excel.bilee.com/x432-77.html

Как разделить текст в MS Excel

• Многие знают, что для того, чтобы объединить текст в двух ячейках достаточно воспользоваться функцией СЦЕПИТЬ (CONCATENATE), однако, как быть, если необходимо не объединить, а наоборот разделить текст в ячейке?
• Смотрите также видеоверсию статьи «Как разделить текст в MS Excel».
• Если количество символов, которое необходимо отделить известно (не важно справа или слева), тогда можно воспользоваться функциями ЛЕВСИМВ (LEFT) или ПРАВСИМВ (RIGHT), в зависимости с какой стороны необходимо выделить определенное количество символов.

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

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

Первый способ – супер быстрый

На самом деле в MS Excel существует встроенная возможность быстрого разделения текста в ячейке, если там присутствует или присутствуют разделитель/разделители (например, простой пробел или запятая). Причем таких разделителей может быть несколько, т.е. текст будет разделятся если в строке присутствует или пробел, или точка с запятой или запятая и т.д.

1. Для этого необходимо выделить ячейки с текстом, который необходимо разделить и воспользоваться командой «Текст по столбцам».
3. В англоязычной версии MS Excel данная команда звучит как «Text to Columns» вкладки «DATA».
5. После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя – пробела можно смело жать «Готово»)
7. А вот и, собственно, результат.

Второй способ – с использованием формул

В такой ситуации понадобится сочетание функций: ПОИСК (SEARCH) и ПСТР (MID). Для начала, с помощью первой находим пробел между словами (между фамилией и именем и именем и отчеством), а потом подключаем вторую для того, чтобы выделить необходимое количество символов. Грубо говоря, первой функцией определяем количество символов, а второй – уже разделяем.

Кроме того, поскольку разделительного знака в конце строки нет, то количество символов в последнем слове (нашем случае – отчестве) вычислить не удастся, но это не проблема, достаточно указать заведомо бОльшее количество символов в качестве аргумента «число_знаков» функции ПСТР, например, 100.

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

Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).

Здесь, в качестве аргументов, используются промежуточные значения, полученные с помощью функции ПОИСК. Для последней колонки количество символов неизвестно, поэтому было взято заведомо бОльшее количество символов (в нашем случае 100).

Теперь попробуем соединить промежуточные расчеты в одну формулу.

Если текст в ячейке необходимо разделить лишь на две части, то необходимо произвести поиск лишь одного пробела (либо другого разделителя, который находится между словами), а для разделения на 4 и больше частей формулу придется усложнить поиском 3го, 4го и т.д. разделителей.

Источник: https://msoffice-prowork.com/kak-razdelit-tekst-v-ms-excel/