Rumus mengambil kata tertentu di Excel

Kadang kita mungkin ingin memisahkan atau mengambil bagian teks dari sebuah sel untuk ditaruh pada sel yang lain. Misalnya, 3 karakter pertama dari sebuah kode atau 4 karakter terakhir dari sebuah teks.

Function atau fungsi untuk mengambil bagian teks di Microsoft Excel adalah LEFT, RIGHT, dan MID.

DAFTAR ISI

1. Kegunaan dan Cara Penulisan Fungsi LEFT, RIGHT, dan MID
2. Cara Menggabungkan Fungsi LEFT, RIGHT, dan MID dengan Fungsi VLOOKUP / HLOOKUP

Bila Anda ingin memisahkan isi sel menjadi beberapa kolom berdasarkan tanda pemisah (koma, spasi, garis miring, huruf, dan lain-lain), maka Anda bisa menggunakan fitur Text to Columns yang disajikan di tutorial ini: Cara Memisahkan Isi Sel Menjadi Beberapa Kolom di Excel.

Catatan:
Pengguna Excel 2013 atau versi di atasnya bisa memakai Flash Fill untuk memisahkan atau menggabungkan data tanpa harus menggunakan rumus. Lihat tutorial lengkapnya di sini: 8 Kegunaan Flash Fill Excel yang Perlu Diketahui.

Kegunaan dan Cara Penulisan Fungsi LEFT, RIGHT, dan MID

Masing-masing cara penulisan dan kegunaannya adalah sebagai berikut:

• LEFT – untuk mengambil bagian data teks dari sebelah kiri.
  Cara penulisan: =LEFT(teks; jumlah_karakter)
  Contoh: =LEFT(“12AG3456”;2) akan menghasilkan 12
• RIGHT – untuk mengambil bagian data teks dari sebelah kanan.
  Cara penulisan: =RIGHT(teks; jumlah_karakter)
  Contoh: =RIGHT(“computer1001.com”; 4) akan menghasilkan .com
• MID – untuk mengambil bagian data teks dari tengah.
  Cara penulisan: =MID(teks; angka_awal; jumlah_karakter)
  Contoh: =MID(“computer1001.com”; 9;4) akan menghasilkan 1001

• Teks adalah string teks yang berisi karakter yang ingin diambil. Teks pada rumus bisa diganti dengan nama sel atau sel referensi. Misalnya: =LEFT(“12AG3456”;2),  bisa ditulis menjadi =LEFT(A1; 2), dimana A1 adalah sel yang berisi string teks yang ingin kita ambil bagian datanya.
• Tergantung pengaturan di komputer masing-masing, pemisah karakter atau argumen pada formula bisa menggunakan:
  • Tanda titik koma (;). Contoh: =MID(“computer1001.com”; 9;4)
  • Atau tanda koma (,). Contoh: =MID(“computer1001.com”, 9,4)
• Bila jumlah karakter pada fungsi LEFT dan RIGHT tidak ditulis, maka Excel akan menganggapnya sebagai 1 (satu) karakter.
  Contoh:
  • =LEFT(“Computer1001.com”) akan menghasilkan C
  • =RIGHT(“Computer1001.com”) akan menghasilkan m

Hasil dari fungsi di atas akan memberikan data dengan format teks. Bila bagian yang dipisahkan berupa angka dan Anda ingin mengkalkulasi data tersebut, kadang Excel bisa melakukan operasi perhitungannya. Namun kadang menghasilkan nilai error.

Oleh karena itu, formula perlu ditambah dengan function VALUE untuk mengubah nilainya menjadi numerik.

Cara penulisan: = VALUE(LEFT(teks; jumlah_karakter))
Contoh: =VALUE(LEFT(D19;2))

Cara Menggabungkan Fungsi LEFT, RIGHT, dan MID dengan Fungsi VLOOKUP / HLOOKUP

Fungsi LEFT, RIGHT, dan MID bisa digunakan untuk mengambil sebagian nilai sel sebagai lookup value, yaitu nilai yang dijadikan kunci dalam pencarian data; dan juga bisa digunakan untuk mengembalikan nilai dari rumus VLOOKUP atau HLOOKUP.

Menggunakan fungsi LEFT, RIGHT, dan MID pada Lookup Value

Tutorial ini menggunakan contoh fungsi MID. Misalnya, kolom Nama pada Tabel Karyawan akan diisi nilai dari Tabel Data Nama. Lookup value menggunakan 3 karakter bagian tengah kolom Kode, yaitu HOL dari 0001HOL-2008 dan seterusnya.

Cara penulisan sintaks atau formula untuk mengisi sel B9 adalah: =VLOOKUP(MID(A9;5;3);$A$2:$B$4;2;FALSE)

• Lookup_value: MID(A9;5;3) mengambil 3 karakter dari sel A9, mulai dari karakter ke-5, yaitu HOL.
• Table_array: range $A$2:$B$4 pada tabel Data Nama.
• Col_index_num: mengambil nilai dari kolom 2 tabel Data Nama.
• Range_lookup: menggunakan metode pengembalian nilai secara tepat (FALSE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama, maka kembalikan nilai error.

Selanjutnya formula pada sel B9 bisa disalin ke sel B10 dan B11.

Hasil formula adalah seperti gambar berikut.

Menggunakan fungsi LEFT, RIGHT, dan MID untuk Mengembalikan Sebagian Nilai VLOOKUP atau HLOOKUP

Tutorial ini menggunakan contoh fungsi LEFT. Misalnya, kolom Kode Angka pada tabel Karyawan akan diisi nilai 4 karakter kiri kolom Kode Tabel Data, yaitu 2122, 4587, dan 3228. Nilai yang menjadi kunci pencarian (lookup value) pada kedua tabel adalah kolom Nama.

Cara penulisan formula untuk mengisi sel B24 adalah: =LEFT(VLOOKUP(A24;$A$17:$B$19;2;FALSE);4)

Penjelasan Formula
Penjelasan untuk formula VLOOKUP(A24;$A$17:$B$19;2;FALSE) adalah sebagai berikut:

• Lookup_value: nilai sel A24 kolom Nama, yaitu Holly.
• Table_array: range $A$17:$B$19 pada Tabel Data.
• Col_index_num: mengambil nilai dari kolom 2 (kolom Kode) Tabel Data. Penggabungan fungsi LEFT yang mengambil 4 karakter sebelah kiri menghasilkan nilai 2122 dari isi sel 2122F.
• Range_lookup: menggunakan metode pengembalian nilai secara tepat (FALSE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama, maka kembalikan nilai error.

Selanjutnya formula pada sel B24 bisa disalin ke sel B25 dan B26. Hasilnya adalah seperti gambar berikut ini.

Penjelasan detil dan contoh VLOOKUP dan HLOOKUP ada di artikel berikut:

• Cara Menggunakan Fungsi VLOOKUP dan HLOOKUP di Excel
• Contoh dan Cara Penulisan Sintaks VLOOKUP dan HLOOKUP di EXCEL

Rekomendasi Artikel

• 3 Cara Menggabungkan Isi Beberapa Sel Excel Menjadi Satu
• Cara Validasi Data yang Diinput pada Lembar Kerja Excel
• Cara Mencegah Duplikat Data dengan Validasi di Excel
• Cara Merubah Huruf menjadi Huruf Kapital atau Kecil di Excel
• Cara Mudah Memindahkan Isi Baris ke Kolom dan Sebaliknya di Tabel Excel

Bagaimana cara memisahkan kata di Excel?

Rumus MID untuk apa?

Rumus Excel Len untuk apa?

Rumus match untuk apa?