Разделить текст построчно по разделителю справа налево

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Разделить текст построчно по разделителю справа налево (Формулы/Formulas)

Xenus91

Дата: Воскресенье, 17.07.2022, 06:33 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 13 ±

Замечаний: 0% ±

Excel 2016

Доброе утро!
Прошу помощи, необходимо текст в столбце А разделить по разделителю "-" и расположить по строкам справа налево
Файл пример прилагаю
Данные в столбцах A,B не статичны, могут располагаться в ниже или выше

К сообщению приложен файл: LENTA01.REP_TR2.xlsx (10.6 Kb)

Сообщение отредактировал Xenus91 - Воскресенье, 17.07.2022, 06:41

Ответить

scriptapplications

Дата: Воскресенье, 17.07.2022, 09:41 | Сообщение № 2

Группа: Проверенные

Ранг: Участник

Сообщений: 68

Репутация: 12 ±

Замечаний: 0% ±

Xenus91,
Для колонки G формула будет такой

Код

=--ПСТР( "-"&$A$2&"-"; НАЙТИ( СИМВОЛ(3); ПОДСТАВИТЬ("-"&$A$2&"-"; "-"; СИМВОЛ(3); ДЛСТР($A$2)-ДЛСТР(ПОДСТАВИТЬ($A$2;"-";""))+1-(СТРОКА()-СТРОКА($G$2))))+1; НАЙТИ( СИМВОЛ(3); ПОДСТАВИТЬ("-"&$A$2&"-"; "-"; СИМВОЛ(3);ДЛСТР($A$2)-ДЛСТР(ПОДСТАВИТЬ($A$2;"-";""))+1-(СТРОКА()-СТРОКА($G$2))+1)) - НАЙТИ( СИМВОЛ(3); ПОДСТАВИТЬ("-"&$A$2&",-"; "-"; СИМВОЛ(3); ДЛСТР($A$2)-ДЛСТР(ПОДСТАВИТЬ($A$2;"-";""))+1-(СТРОКА()-СТРОКА($G$2))))-1)

Подробнее о формуле выделения подстроки (с определённым номером) через разделитель

К сообщению приложен файл: LENTA01.REP_TR.xlsx (11.5 Kb)

Сообщение отредактировал scriptapplications - Воскресенье, 17.07.2022, 10:23

Ответить

Gustav

Дата: Воскресенье, 17.07.2022, 13:58 | Сообщение № 3

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Ещё можно пойти таким путём. Сначала скопировать колонку А в колонку G, в которой затем выполнить команду "Текст по столбцам" с разделителем "-", в результате чего данные разбросаются по столбцам G:N.

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

Код

=ЕСЛИОШИБКА(ИНДЕКС(G:N;ПОИСКПОЗ("*";$A$1:A2;-1);C2);"")

К сообщению приложен файл: LENTA_02.xlsx (11.2 Kb)

МОИ: Ник, Tip box: 41001663842605

Ответить

Egyptian

Дата: Воскресенье, 17.07.2022, 21:44 | Сообщение № 4

Группа: Проверенные

Ранг: Ветеран

Сообщений: 526

Репутация: 193 ±

Замечаний: 0% ±

Excel 2013/2016

Для столбца D одной формулой.

Код

=IF(ROWS($A$2:$A2)<=SUMPRODUCT(LEN(A$2:A$18)-LEN(SUBSTITUTE(A$2:A$18;"-";)))+2;FILTERXML("< t >< s >"&SUBSTITUTE(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2);"-";"< /s>< s >")&"< /s >< /t >";"//s[last()-"&(MOD(ROWS($D$2:D2)-1;LEN(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2))-LEN(SUBSTITUTE(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2);"-";""))+1)+1)-1&"]");"")

К сообщению приложен файл: 6300526.xlsx (11.2 Kb)

Сообщение отредактировал Egyptian - Воскресенье, 17.07.2022, 21:45

Ответить

Xenus91

Дата: Воскресенье, 17.07.2022, 21:53 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 13 ±

Замечаний: 0% ±

Excel 2016

Gustav, большое спасибо, доберусь до компьютера попробую, на телефоне пока глянул, получается формула работает для фикс ячейки, а можно как-то разбить этот текст по разделителю, собрать в массив и вывести n элемент массива?
Например текст для обработки будет скопирован вниз по столбцу и напротив будут идти номера элементов по убыванию как они сейчас есть и рядом по номеру элемент будет отображаться то что мне как раз и нужно

Ответить

Xenus91

Дата: Воскресенье, 17.07.2022, 21:56 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 13 ±

Замечаний: 0% ±

Excel 2016

Egyptian, спасибо попробую на пк, на мобилке не распознает выдаёт ошибку имени

Ответить

Gustav

Дата: Воскресенье, 17.07.2022, 22:59 | Сообщение № 7

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата Xenus91, 17.07.2022 в 21:53, в сообщении № 5 (

)

а можно как-то разбить этот текст по разделителю, собрать в массив и вывести n элемент массива?

Можно, если располагаете версией Excel, содержащей фукцию TEXTSPLIT. Вроде, она есть в подписочной версии Excel для Microsoft 365. Эта функция внутри формулы формирует виртуальный массив, фактически делая то же, что и команда "Текст по столбцам".

Я сужу только по опубликованным в Сети материалам, доступа к версии 365 я не имею. [offtop]Но у меня, как у многих, есть свободный доступ к Таблицам Google, в которых УЖЕ есть аналогичная функция SPLIT. Можете попробовать с ней, формула для той же ячейки D2 будет такая (протягиваемая вниз, как и в Excel):
[vba]

Код

=ArrayFormula(IF(C2<1;""; INDEX(IFERROR(SPLIT($A$1:$A$200;"-"););MATCH("*";$A$1:A2;-1);C2)))

[/vba]

МОИ: Ник, Tip box: 41001663842605

Ответить

Egyptian

Дата: Понедельник, 18.07.2022, 13:15 | Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 526

Репутация: 193 ±

Замечаний: 0% ±

Excel 2013/2016

Xenus91, Обнаружил пару косяков, выкладываю обновленный вариант.

Код

=IF(ROWS($A$2:$A2)<=SUMPRODUCT(LEN(A$2:A$30)-LEN(SUBSTITUTE(A$2:A$30;"-";)))+COUNTA(A$2:A$30);FILTERXML("< t >< s >"&SUBSTITUTE(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2);"-";"< /s >< s >")&"< /s >< /t >";"//s[last()-"&(MOD(ROW()-LOOKUP(2;1/($A$2:$A2<>"");ROW($A$2:$A2));LEN(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2))-LEN(SUBSTITUTE(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2);"-";""))+1)+1)-1&"]");"")

и еще:

Код

=IF(ROWS($A$2:$A2)<=SUMPRODUCT(LEN(TRIM(SUBSTITUTE(A$2:A$30;"-";" ")))-LEN(SUBSTITUTE(A$2:A$30;"-";"")))+COUNTA(A$2:A$30);MID(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2);(LEN(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2))-2)-(5*MOD(ROW()-LOOKUP(2;1/($A$2:$A2<>"");ROW($A$2:$A2));LEN(SUBSTITUTE(LOOKUP(2;1/(ROW($A$2:$A2)/(A$2:A2<>""));$A$2:$A2)&"-";"-";))/4)+1);4);"")

К сообщению приложен файл: 1111117.xlsx (13.4 Kb)

Сообщение отредактировал Egyptian - Понедельник, 18.07.2022, 13:17

Ответить

Xenus91

Дата: Среда, 27.07.2022, 08:50 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 13 ±

Замечаний: 0% ±

Excel 2016

Всем большое спасибо за помощь!

Ответить

_Boroda_

Дата: Среда, 27.07.2022, 10:55 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

Репутация: 6505 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Еще вариант формулой массива

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);"-";ПОВТОР(" ";99));99*(СЧЁТ(1/(ПСТР(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);СТРОКА($1:$99);1)="-"))-СТРОКА()+ПОИСКПОЗ("*";A$1:A2;-1))+1;99))

или обычной

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);"-";ПОВТОР(" ";99));99*(СЧЁТ(ИНДЕКС(1/(ПСТР(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);СТРОКА($1:$99);1)="-");))-СТРОКА()+ПОИСКПОЗ("*";A$1:A2;-1))+1;99))

К сообщению приложен файл: 1111117_1.xlsx (15.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Разделить текст построчно по разделителю справа налево (Формулы/Formulas)

Страница 1 из 1
1