Новая формула немного длиннее оригинальной, поскольку функцию MID(…)
необходимо скопировать и использовать еще два раза.
Массив введите (Ctrl+Shift+Enter) следующую формулу в F2
и скопируйте-вставьте / заполните вниз в оставшуюся часть столбца:
{=
TEXTJOIN(
";",
TRUE,
IF(
ISNUMBER(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
)
),
INDEX(
(B:B),
N(IF(1,
MATCH(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
),
(A:A),
0
)
))
),
TRIM(
MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
)
)
)
)}
Обратите внимание, что изменение в формуле - это просто добавленная функция IF()
которая проверяет, является ли извлеченное значение числом или текстом, и обрабатывает его по-разному. Текстовое значение возвращается как есть, тогда как числовое значение используется для поиска, как и прежде.
Модифицированная более простая формула Excel 2016 (только для Windows):
{=TEXTJOIN(";",TRUE,IF(ISNUMBER(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")),INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))),FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b")))}