Formuła ogólna
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Podsumowanie
Aby podzielić tekst według dowolnego separatora (przecinek, spacja, kreska itp.), Możesz użyć formuły opartej na funkcjach TRIM, MID, SUBSTITUTE, REPT i LEN. W przedstawionym przykładzie formuła w C5 to:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Uwaga: odniesienia do B5 i C4 są odniesieniami mieszanymi, aby umożliwić skopiowanie wzoru w poprzek i w dół.
Wyjaśnienie
Istotą tej formuły jest zastąpienie danego separatora dużą liczbą spacji za pomocą funkcji SUBSTITUTE i REPT, a następnie użycie funkcji MID w celu wyodrębnienia tekstu związanego z „n-tym wystąpieniem” oraz funkcji TRIM w celu usunięcia dodatkowego spacji.
W tym fragmencie separator (separator) jest zastępowany liczbą spacji równą całkowitej długości ciągu:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Następnie formuła używa funkcji MID do wyodrębnienia n-tego podciągu. Punkt początkowy jest obliczany za pomocą poniższego kodu, gdzie N oznacza „n-ty”:
(N-1)*LEN(A1)+1
Łączna liczba wyodrębnionych znaków jest równa długości pełnego ciągu tekstowego. Następnie funkcja TRIM usuwa wszystkie dodatkowe spacje i zwraca tylko n-ty ciąg.
Wyodrębnij tylko jedną instancję
Chociaż przykład jest skonfigurowany do wyodrębniania 5 podciągów z tekstu w kolumnie B, możesz łatwo wyodrębnić tylko 1 wystąpienie. Na przykład, aby wyodrębnić tylko czwartą pozycję (miasto), możesz użyć:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Funkcja Tekst do kolumn
W przypadku ręcznych, jednorazowych konwersji program Excel ma wbudowaną funkcję o nazwie „Tekst na kolumny”, która umożliwia dzielenie tekstu w komórkach za pomocą wybranego separatora. Tę funkcję znajdziesz na karcie Dane na wstążce w sekcji Narzędzia danych.
