Formula Excel: Pisahkan kalimat menjadi kata-kata
Di Excel, seperti yang kita ketahui, Text to Columns dapat membagi kalimat menjadi sel berdasarkan pembatas. Namun terkadang, Anda ingin menggunakan rumus untuk menyelesaikan pekerjaan. Disini tutorial ini memberikan formula array yang bisa mengatasinya.
Rumus umum:
=MID(" "&$TXT&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))-1) |
kasus
Number: the sentence or text string you want to split to words in separated cells. |
Bagaimana rumus ini bekerja
Ambil sel B3 sebagai contoh, Anda dapat menggunakan rumus di bawah ini:
=MID(" "&$B3&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))-1) |
Tekan Shift + Ctrl + Masuk untuk mengekstrak kata pertama, lalu seret gagang isian ke kanan hingga semua kata diekstraksi.
Penjelasan
MID fungsi mengekstrak substring dari string teks berdasarkan posisi awal yang ditentukan dan jumlah karakter.
ROW fungsi mengembalikan jumlah baris, di sini kami mengaturnya untuk mengembalikan array dari 1 hingga 99.
Lalu rumusnya MID ("" & $ B2 & "", BARIS ($ 1: $ 99), 1)
= MID ("Kelas A Kelas 3 Lily Core", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19 ; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44 ; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69 ; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94 ; 95; 96; 97; 98; 99}, 1)
= {""; "C"; "l"; "a"; "s"; "s"; ""; "A"; ""; "G"; "r"; "a"; "d" ; "e"; ""; "3"; ""; "L"; "i"; "l"; "y"; ""; "C"; "o"; "r"; "e"; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; "" ; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " ";" ";" ";" ";" ";" ";
""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; "" ; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " "}
SEARCH fungsi mengembalikan lokasi karakter atau teks tertentu dari string teks yang diberikan. Jika string tidak ditemukan fungsi mengembalikan nilai kesalahan.
Rumusnya PENCARIAN (MID ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")
= {1; #VALUE!; # NILAI!; # NILAI!; # NILAI!; # NILAI!; 1; #VALU!; 1; #VALU!; # NILAI !;
#VALUE!; # NILAI!; # NILAI!; 1; #VALUE!; 1; #VALUE!; # NILAI!; # NILAI!; # NILAI!; 1;
#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1}
ISERROR fungsi mengembalikan TRUE jika nilainya adalah kesalahan.
Kemudian formula ISEROR (PENCARIAN (MENENGAH ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), ""))
= {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;
TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
SALAH}
IF fungsi tes untuk kondisi tertentu. Di sini ada tiga argumen, yang pertama harus merupakan ekspresi logis, jika argumen pertama adalah "BENAR", maka argumen kedua ditampilkan, dan jika argumen pertama adalah "SALAH", argumen 3 ditampilkan.
Rumusnya JIKA (ISEROR (PENCARIAN (MENENGAH ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99))
={1;"";"";"";"";"";7;"";9;"";"";"";"";"";15;"";17;"";"";"";"";22;"";"";"";"";27;28;29;30;31;32;33;34;35;
36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;
64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;
93;94;95;96;97;98;99}
SMALL fungsi lalu mengembalikan nilai terkecil dalam larik.
COLUMN fungsi mengembalikan jumlah kolom yang rumus muncul atau mengembalikan nomor kolom referensi yang diberikan.
Kemudian
KECIL (JIKA (ISEROR (CARI (MENENGAH ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99)), KOLOM (A1))
= {1}
Lalu rumusnya
KECIL (JIKA (ISEROR (CARI (MENENGAH ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99)), KOLOM (A1) +1 ) - KECIL (JIKA (ISEROR (PENCARIAN (MENENGAH ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99)), KOLOM (A1) )
= {7-1}
= {6}
Kemudian formula
MID ("" & $ B3 & "", KECIL (JIKA (ISEROR (CARI (CARI (MENCARI ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99) +1), KOLOM (A1)), KECIL (JIKA (ISEROR (CARI (MENCARI (MID ("" & $ B3 & "", BARIS ($ 1: $ 99), 1), "")), "", BARIS ( $ 1: $ 99)), COLUMN (A1) +1) -SMALL (IF (ISERROR (SEARCH (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", BARIS ($ 1: $ 99)), KOLOM (A1)) - 1)
= MID ("" & $ B3 & "", 2,6-1)
= {Class}
File Contoh
Klik untuk mendownload file contoh
Rumus Relatif
- Ekstrak dari kanan hingga karakter
Di sini memperkenalkan rumus untuk mengekstrak teks dari sisi kanan teks tertentu hingga karakter tertentu. - Pangkas Teks Ke N Kata
Di sini memperkenalkan rumus untuk mengekstrak n kata dari sisi kiri string teks. - Tambahkan leaing nol untuk menetapkan panjang
Tutorial ini memberikan rumus untuk menambahkan nol di depan untuk membuat teks dengan panjang yang sama. - Bagi dolar dan sen
Untuk membagi dolar dan sen menjadi dua kolom dengan rumus di Excel.
Fungsi Relatif
- Fungsi MID
Ekstrak bagian teks dari nilai sel berdasarkan lokasi dan panjangnya. - Fungsi COLUMN
Mengembalikan jumlah kolom yang rumusnya muncul atau mengembalikan nomor kolom. - Fungsi ROW
Dapatkan jumlah baris. - Fungsi SEARCH
Temukan posisi karakter atau substring tertentu dari string teks yang diberikan. - JIKA berfungsi
Uji untuk kondisi tertentu.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.