Fungsi teks: LEFT, MID, TRIM, CONCAT, dan lainnya

Idenya

Kafenya sekarang punya program member, dan tim marketing ingin mengirim pesan pribadi ke pelanggan: "Hai Ayu, terima kasih sudah mampir ke cabang Jakarta kami!" Daftar pelanggannya diekspor dari sistem kasir, dan saat dibuka, isinya berantakan. Ada nama yang ditulis huruf kapital semua, ada yang huruf kecil semua, dan ada yang diawali spasi berlebih. Setiap kode pelanggan memadatkan cabang dan nomor pelanggan dalam satu sel. Nomor telepon ditulis dengan tanda hubung di beberapa baris dan spasi di baris lain, padahal aplikasi pengirim pesannya hanya menerima angka polos.

Kamu bisa merapikan semuanya secara manual, dan untuk lima pelanggan mungkin malah lebih cepat. Tapi daftar aslinya berisi ribuan baris, dan ekspor baru datang setiap minggu, sama berantakannya dengan yang lalu. Fungsi teks memungkinkanmu menulis langkah pembersihannya sekali saja, dalam bentuk rumus. Minggu depan kamu tinggal menempelkan ekspor baru, dan kolom-kolom yang bersih terisi sendiri.

Sheet modul ini adalah potongan kecil dari ekspor tersebut:

Memotong teks: LEFT, RIGHT, MID, LEN

Mulai dari kode pelanggan. Setiap kode terdiri dari tiga bagian yang selalu ada di tempat yang sama: 3 huruf cabang, tanda hubung, dan 4 digit nomor pelanggan. JKT-0012 artinya pelanggan nomor 12 di cabang Jakarta. Untuk memakai bagian-bagian itu secara terpisah, misalnya untuk menghitung pelanggan per cabang, kamu perlu memotong kodenya. Keempat fungsi ini yang mengerjakan pemotongannya:

# Kode cabang: 3 karakter pertama
F2: =LEFT(A2, 3)
# Nomor pelanggan: 4 karakter terakhir
G2: =RIGHT(A2, 4)
# 4 karakter mulai dari posisi 5
H2: =MID(A2, 5, 4)
# Berapa karakter nama mentahnya, termasuk spasi
I2: =LEN(B2)
# Berapa karakter "Kopi Susu"
I3: =LEN("Kopi Susu")

F2: JKT
G2: 0012
H2: 0012
I2: 14
I3: 9

LEFT mengambil 3 karakter pertama, JKT, dan RIGHT mengambil 4 karakter terakhir, 0012. MID mendapatkan nomor yang sama dari tengah: mulai dari karakter ke-5 (tepat setelah tanda hubung) lalu ambil 4. Untuk susunan yang tetap seperti ini, RIGHT lebih sederhana, tapi MID-lah yang kamu pakai kalau bagian yang kamu mau terjepit di tengah.

LEN menghitung jumlah karakter, dan fungsi ini mengungkap masalah tersembunyi. LEN(B2) bernilai 14, padahal "ayu lestari" hanya 11 karakter. Selisihnya adalah spasi nyasar sebelum dan sesudah nama, yang tidak terlihat tapi benar-benar ada. Spasi seperti inilah yang nanti membuat nama gagal dicocokkan, jadi ada baiknya kamu tahu cara menemukannya.

Satu detail lagi tentang nomor pelanggan. Apa pun yang dikembalikan fungsi teks berupa teks, meskipun terlihat seperti angka. 0012 dari RIGHT adalah teks "0012", bukan angka dua belas, jadi Excel tidak akan mengurutkan atau menjumlahkannya seperti yang kamu harapkan. VALUE mengubah digit kembali menjadi angka sungguhan:

# Ubah teks "0012" menjadi angka 12
J2: =VALUE(RIGHT(A2, 4))

J2: 12

Membersihkan: TRIM, PROPER, UPPER, LOWER

Sekarang nama-namanya. Ada dua masalah terpisah: spasi berlebih, dan huruf kapital yang berantakan. TRIM menangani yang pertama dan PROPER yang kedua. Karena jawaban sebuah fungsi bisa langsung diberikan ke fungsi lain, kamu bisa mengerjakan keduanya dalam satu rumus: TRIM merapikan spasinya dulu, lalu PROPER membetulkan huruf kapital dari hasil TRIM:

F2:F6: =PROPER(TRIM(B2))
# Nama kota dalam huruf kapital
G2: =UPPER(C2)
# Kode dalam huruf kecil
G3: =LOWER(A3)

F2: Ayu Lestari
F3: Budi Santoso
F4: Citra Dewi
F5: Dewi Anggraini
F6: Eko Prasetyo
G2: JAKARTA
G3: bdg-0045

Mengganti: SUBSTITUTE

Semua nama sekarang terlihat benar, termasuk milik Citra, yang tadinya punya dua spasi di antara nama depan dan belakangnya. TRIM mengubah deretan spasi di dalam teks menjadi satu spasi saja.

Berikutnya, nomor telepon. Aplikasi pengirim pesan butuh angka polos, jadi tanda hubung dan spasinya harus dibuang. SUBSTITUTE(teks, lama, baru) mencari setiap kemunculan lama di dalam teks lalu menggantinya dengan baru. Kalau kamu menggantinya dengan "", yaitu teks kosong, artinya kamu menghapusnya. Ada nomor yang memakai tanda hubung dan ada yang memakai spasi, jadi supaya aman kamu membuang keduanya. Untuk itu dibutuhkan dua SUBSTITUTE, satu di dalam yang lain: yang di dalam membuang tanda hubung, dan yang di luar membuang spasi dari hasilnya:

# Hapus tanda hubung dari nomor telepon
F2: =SUBSTITUTE(D2, "-", "")
F3:F4: =SUBSTITUTE(SUBSTITUTE(D3, "-", ""), " ", "")

F2: 081234567890
F3: 081322221111
F4: 085712345678

Argumen keempat hanya mengganti kemunculan ke-n: SUBSTITUTE(D2, "-", ".", 1) hanya menukar tanda hubung pertama.

Potongan dengan panjang berbeda: FIND dan SEARCH

Untuk pesan pribadinya kamu hanya butuh nama depan: "Hai Dewi", bukan "Hai Dewi Anggraini". Kode pelanggan mudah dipotong karena semua kode punya susunan yang sama. Nama tidak begitu: "Dewi" empat huruf dan "Eko" tiga huruf, jadi kamu tidak bisa begitu saja menyuruh LEFT mengambil 4 karakter.

Yang dimiliki setiap nama adalah spasi di antara nama depan dan nama belakang. FIND(teks_dicari, teks) memberi tahu posisi pertama kemunculan sesuatu, jadi kamu bisa menemukan spasinya lalu memotong tepat sebelumnya. Dengan begitu rumusnya menghitung sendiri untuk setiap nama, tanpa kamu harus menghitung manual:

# Posisi spasi di nama Dewi yang sudah rapi
P2: =FIND(" ", F5)
# Nama depan: semua yang ada sebelum spasi
P3: =LEFT(F5, FIND(" ", F5) - 1)
# Nama belakang: semua yang ada setelah spasi
P4: =MID(F5, FIND(" ", F5) + 1, 100)

P2: 5
P3: Dewi
P4: Anggraini

Spasi di "Dewi Anggraini" adalah karakter ke-5. Jadi nama depannya adalah 4 karakter pertama, dan karena itulah P3 mengambil FIND(...) - 1: satu kurang dari posisi spasinya. Nama belakangnya dimulai tepat setelah spasi, di FIND(...) + 1. Untuk panjangnya, MID diminta mengambil 100 karakter, trik umum untuk "sisa teksnya". Sisanya tidak sampai 100 karakter, jadi MID berhenti sendiri di akhir teks.

SEARCH bekerja seperti FIND tapi tidak membedakan huruf besar dan kecil, serta mengerti wildcard * dan ?. Jika teksnya tidak ditemukan, keduanya mengembalikan #VALUE!, yang bisa kamu tangkap dengan IFERROR (modul 6).

Menggabungkan: &, CONCAT, TEXTJOIN

Terakhir, potongan-potongan yang sudah bersih harus disatukan kembali menjadi pesan. Simbol & merekatkan potongan teks satu demi satu. Apa pun yang ingin kamu taruh di antaranya, seperti spasi atau tanda kurung, ditambahkan sebagai teks di dalam tanda kutip:

# Nama yang rapi dengan kota di dalam kurung
N2: =PROPER(TRIM(B2)) & " (" & PROPER(C2) & ")"
# Gabungkan cabang dan nomor dengan garis miring
N3: =CONCAT(LEFT(A3, 3), "/", RIGHT(A3, 4))

N2: Ayu Lestari (Jakarta)
N3: BDG/0045

N2 merapikan spasi dan huruf kapital nama, lalu menambahkan " (", nama kota dengan huruf kapital yang benar, dan ")". CONCAT melakukan pekerjaan yang sama dengan &, tapi potongannya ditulis sebagai argumen yang dipisahkan koma, dan fungsi ini juga bisa menerima satu range sekaligus.

Saat kamu menggabungkan sebuah daftar, TEXTJOIN(pemisah, abaikan_kosong, teks...) yang paling mudah. Kamu memberi pemisahnya sekali, lalu fungsi ini menaruhnya di antara item-itemnya, jadi tidak ada koma nyasar di akhir. Dengan TRUE sebagai argumen kedua, sel kosong juga dilewati:

# Tiga kode dalam satu sel, dipisah koma
O2: =TEXTJOIN(", ", TRUE, A2:A4)

O2: JKT-0012, BDG-0045, SBY-0103

Kenapa rumus, bukan memperbaiki manual?

Kembali ke awal: untuk lima pelanggan, mengetik ulang nama secara manual sebenarnya lebih cepat. Tapi kamu tidak bisa mengetik ulang 5.000 nama, dan kamu jelas tidak mau mengulanginya minggu depan saat ekspor baru datang. Susunan yang biasa dipakai adalah membiarkan ekspor mentah tetap utuh di kolomnya sendiri, lalu menaruh rumus pembersih di kolom bantu di sebelahnya. Saat ekspor baru datang, kamu menempelkannya di atas data mentah yang lama, dan kolom-kolom yang bersih terbentuk ulang dengan sendirinya.

Kadang kamu butuh hasil bersihnya sebagai teks biasa, tanpa rumus, misalnya untuk mengirim daftarnya ke tim marketing. Salin kolom yang bersih lalu gunakan Paste Special → Values, yang menempelkan apa yang ditampilkan rumusnya, bukan rumusnya sendiri.

Rangkuman

• LEFT, RIGHT, MID memotong bagian teks; LEN menghitung karakter; VALUE mengubah teks digit menjadi angka.
• TRIM menghapus spasi berlebih; PROPER, UPPER, LOWER merapikan huruf kapital.
• SUBSTITUTE(teks, lama, baru) mengganti (atau, dengan "", menghapus) teks; sarangkan untuk beberapa penggantian.
• & dan CONCAT menggabungkan teks; TEXTJOIN menggabungkan range dengan pemisah dan bisa melewati sel kosong.
• Simpan rumus pembersih di sebelah data mentah supaya terbentuk ulang di setiap ekspor baru.