Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Разделить текст построчно по разделителю справа налево - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Разделить текст построчно по разделителю справа налево (Формулы/Formulas)
Разделить текст построчно по разделителю справа налево
Xenus91 Дата: Воскресенье, 17.07.2022, 06:33 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 13 ±
Замечаний: 0% ±

Excel 2016
Доброе утро!
Прошу помощи, необходимо текст в столбце А разделить по разделителю "-" и расположить по строкам справа налево
Файл пример прилагаю
Данные в столбцах A,B не статичны, могут располагаться в ниже или выше
К сообщению приложен файл: LENTA01.REP_TR2.xlsx (10.6 Kb)


Сообщение отредактировал Xenus91 - Воскресенье, 17.07.2022, 06:41
 
Ответить
СообщениеДоброе утро!
Прошу помощи, необходимо текст в столбце А разделить по разделителю "-" и расположить по строкам справа налево
Файл пример прилагаю
Данные в столбцах A,B не статичны, могут располагаться в ниже или выше
Автор - Xenus91
Дата добавления - 17.07.2022 в 06:33
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
 
Ответить
Сообщение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)


Подробнее о формуле выделения подстроки (с определённым номером) через разделитель
Автор - scriptapplications
Дата добавления - 17.07.2022 в 09:41
Gustav Дата: Воскресенье, 17.07.2022, 13:58 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2728
Репутация: 1132 ±
Замечаний: 0% ±

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

Далее в ячейку D2 помещаем довольно простую формулу, которую затем протягиваем вниз на нужное количество строк:
Код
=ЕСЛИОШИБКА(ИНДЕКС(G:N;ПОИСКПОЗ("*";$A$1:A2;-1);C2);"")
Автор - Gustav
Дата добавления - 17.07.2022 в 13:58
Egyptian Дата: Воскресенье, 17.07.2022, 21:44 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 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
 
Ответить
СообщениеДля столбца 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&"]");"")
Автор - Egyptian
Дата добавления - 17.07.2022 в 21:44
Xenus91 Дата: Воскресенье, 17.07.2022, 21:53 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 13 ±
Замечаний: 0% ±

Excel 2016
Gustav, большое спасибо, доберусь до компьютера попробую, на телефоне пока глянул, получается формула работает для фикс ячейки, а можно как-то разбить этот текст по разделителю, собрать в массив и вывести n элемент массива?
Например текст для обработки будет скопирован вниз по столбцу и напротив будут идти номера элементов по убыванию как они сейчас есть и рядом по номеру элемент будет отображаться то что мне как раз и нужно
 
Ответить
СообщениеGustav, большое спасибо, доберусь до компьютера попробую, на телефоне пока глянул, получается формула работает для фикс ячейки, а можно как-то разбить этот текст по разделителю, собрать в массив и вывести n элемент массива?
Например текст для обработки будет скопирован вниз по столбцу и напротив будут идти номера элементов по убыванию как они сейчас есть и рядом по номеру элемент будет отображаться то что мне как раз и нужно
Автор - Xenus91
Дата добавления - 17.07.2022 в 21:53
Xenus91 Дата: Воскресенье, 17.07.2022, 21:56 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 13 ±
Замечаний: 0% ±

Excel 2016
Egyptian, спасибо попробую на пк, на мобилке не распознает выдаёт ошибку имени
 
Ответить
СообщениеEgyptian, спасибо попробую на пк, на мобилке не распознает выдаёт ошибку имени
Автор - Xenus91
Дата добавления - 17.07.2022 в 21:56
Gustav Дата: Воскресенье, 17.07.2022, 22:59 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2728
Репутация: 1132 ±
Замечаний: 0% ±

начинал с 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
 
Ответить
Сообщение
Цитата 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]
Автор - Gustav
Дата добавления - 17.07.2022 в 22:59
Egyptian Дата: Понедельник, 18.07.2022, 13:15 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 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, Обнаружил пару косяков, выкладываю обновленный вариант.
Код
=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);"")
Автор - Egyptian
Дата добавления - 18.07.2022 в 13:15
Xenus91 Дата: Среда, 27.07.2022, 08:50 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 13 ±
Замечаний: 0% ±

Excel 2016
Всем большое спасибо за помощь!
 
Ответить
СообщениеВсем большое спасибо за помощь!
Автор - Xenus91
Дата добавления - 27.07.2022 в 08:50
_Boroda_ Дата: Среда, 27.07.2022, 10:55 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16672
Репутация: 6479 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеЕще вариант формулой массива
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПРОСМОТР(;-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))
Автор - _Boroda_
Дата добавления - 27.07.2022 в 10:55
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Разделить текст построчно по разделителю справа налево (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!