Formula umum
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Ringkasan
Untuk memisahkan teks pada pembatas arbitrer (koma, spasi, pipa, dll.), Anda dapat menggunakan rumus berdasarkan fungsi TRIM, MID, SUBSTITUTE, REPT, dan LEN. Pada contoh yang ditunjukkan, rumus di C5 adalah:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Catatan: referensi ke B5 dan C4 adalah referensi campuran untuk memungkinkan rumus disalin ke depan dan ke bawah.
Penjelasan
Inti dari rumus ini adalah mengganti pemisah yang diberikan dengan sejumlah besar spasi menggunakan SUBSTITUTE dan REPT, lalu gunakan fungsi MID untuk mengekstrak teks yang terkait dengan "kejadian n" dan fungsi TRIM untuk menghilangkan spasi ekstra.
Dalam potongan ini, pemisah (delim) diganti dengan jumlah spasi yang sama dengan total panjang string:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Kemudian rumus tersebut menggunakan fungsi MID untuk mengekstrak substring ke-n. Titik awal dihitung dengan kode di bawah ini, di mana N mewakili "nth":
(N-1)*LEN(A1)+1
Total karakter yang diekstrak sama dengan panjang string teks lengkap. Fungsi TRIM kemudian menghapus semua spasi ekstra dan hanya mengembalikan string ke-n.
Ekstrak hanya satu contoh
Meskipun contoh disiapkan untuk mengekstrak 5 substring dari teks di kolom B, Anda dapat dengan mudah mengekstrak hanya 1 contoh. Misalnya, untuk mengekstrak hanya item ke-4 (kota), Anda dapat menggunakan:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Fitur Teks ke Kolom
Untuk konversi manual satu kali, Excel memiliki fitur bawaan yang disebut "Teks ke Kolom" yang dapat memisahkan teks dalam sel dengan pembatas pilihan Anda. Anda akan menemukan fitur ini di tab Data pada pita di bagian Alat data.
