Новая формула немного длиннее оригинальной, так как 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")))}