Cara Menggunakan Function untuk Memisahkan atau Mengambil Bagian Teks di Excel


Tutorial: Excel 2007, 2010, 2013.

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.

Menggunakan Function LEFT, MID, dan RIGHT untuk Mengambil Bagian Teks Excel

 

 

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.

 

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

Contoh penulisan dan hasil functionn LEFT, MID, dan RIGHT


Catatan:

  • 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.
Lookup value VLOOKUP dengan function Mid

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

Penjelasan Formula

  • 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.
Tampilan seluruh formula function MID dan VLOOKUP

Hasil formula adalah seperti gambar berikut.
Tampilan hasil formula function MID dan VLOOKUP

 

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.
Tampilan seluruh formula function LEFT dan VLOOKUP

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

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.
Tampilan hasil function LEFT dan VLOOKUP

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

 

Rekomendasi Artikel

 

Share this

Related Posts

Previous
Next Post »

34 comments

Tulis Komentar
09 April, 2014 07:56 delete

Hi Gan, Saya mohon bantuannya....
Saya ingin menjumlahkan angka di excell tapi penulisanya terpisah contoh 156 + 235 = 391 penulisannya ratusan puluhan dan satuan itu menjadi 3 kolom dan penjumlahannya juga demikian, itu caranya gimana Gan..
Saya sangat mohon bantuannya Gan.....
Thanks.....

Reply
10 April, 2014 10:57 delete

Coba gunakan formula berikut:

Ratusan: =INT(RIGHT(INT(A1); 3)/100)
Puluhan: =INT(RIGHT(INT(A1); 2)/10)
Satuan: =INT(RIGHT(INT(A1); 1))

dimana A1 adalah sel yang berisi angka 156 atau 235 dan ganti titik koma (;) pada formula dengan tanda koma bila komputer Anda menggunakan pemisah tersebut.

Reply
23 August, 2014 22:25 delete

Mohon bantuan..
Gmana caranya mau memisahkan tanggal dan jam. Misalnya, di A1 isinya 12/08/2014 08:00 dengan format cell custom dd/mm/yyyy hh:mm
Mau dipisahkan di B1 12/08/2014
Trus di C1 08:00
Sudah coba pake MID tp yg muncul hanya angka-angka, meskipun format disamakan atau pun format yg lain.
Langsung dibalas ya.. Atau email ke dsufihar@gmail.com
Terma kasih banyak boss

Reply
Anonymous
03 February, 2015 10:53 delete

ADMIN MAU TY YA...GIMANA MEMINDAH DATA DI SHIFF A KE SHIFF B. DIDALAMMYA ADA DATA DENGAN RUMUS. DATA AWAL KOLOM MENJADI DI BARIS DENGAN RUMUS, BUKAN DENGAN COPY PASTE TRANSPOSE...TRIM YA ADMIN.... COMPUTER 1001 EMANG MEMBATU KERJA KITA....TRIMMSSSS...

Reply
24 March, 2015 17:55 delete

terimakasih banyak sangat membantu sekali :D

Reply
Anonymous
06 May, 2015 22:44 delete

Terima kasih banyak atas tips2 nya dalam menggunakan aplikasi excel, sangat bermanfaat... Salam dan sukses selalu

Reply
16 May, 2015 08:42 delete

kalau mau ambi 2 karakter gimana gan? semisal "AN-7U9V-ALK" karakter yang mau diambil itu "UV" itu gimana ya gan? pake rumus apa? thanks.

Reply
17 May, 2015 09:48 delete

Bagaimana cara memilih huruf (hanya nama pelanggan) pada kalimat berikut :
PLN50.32014265147 Hrg=50200 SUKSES SN: 1401-1998-4958-0245-7148/SABUDIN/R1/900/72,5. Sal Rp.1143096

menggunakan =MID(C2,65,7) jawabannya SABUDIN

Pertanyaannya :
Bagaimana jika nama pelanggan tersebut terdiri dari 4-12 huruf, apakah rumus harus berganti-ganti atau ada cara lain yang bisa memisahkan huruf (nama pelanggan) sampai tanda "/".
Mohon pencerahannya, terimakasih..

Reply
20 May, 2015 20:22 delete

Karena ada beberapa tanda /, coba gunakan Text to Column. Dengan cara ini Anda bisa mendapatkan nama pelanggan dengan mudah tanpa tergantung pada jumlah huruf.
Tutorialnya ada di sini: Cara Memisahkan Isi Sel Menjadi Beberapa Kolom di Excel

Reply
Anonymous
10 July, 2015 15:03 delete

Om, bagaimana carany menjumlahkan angka yg di depan huruf. misalnya ada dua cel isinya 1DP dan 2AL, maka saya ingin dalam 1 sel muncul angka 3. Namun cel yg ingin saya jumlahnya banyak ada 60 cell

Reply
16 July, 2015 13:30 delete

Coba formula ini dan akhiri dengan menekan tombol CTRL + SHIFT + ENTER:
=SUM(LEFT(A1:A2;1)*1)

Reply
29 July, 2015 13:39 delete

Halo Mas,..

Mohon bantuannya bagaimana cara menjumlahkan secara terpisah dari harga satuan dalam satu kolom yang berbeda mata uangnya...

salam

Reply
08 September, 2015 12:15 delete

bagai mana cara menjumlahkan 1a+1b+1c

Reply
Anonymous
30 September, 2015 10:45 delete

misal"AN-7U9V-ALK" berada di cell A1, maka bisa menggunakan rumus =MID(A1;5;1)&MID(A1;7;1)

Reply
08 December, 2015 12:54 delete

Halo Gan, Bagaimana cara menyatukan dua nama Pada Satu Cel yang Mengunakan rumus hlookup left dan right. Saya mencoba Rumus =Concatenate(hlookup(left(B3;2);f3:j4;2;false);vlooup(right(B3;3);A5:C9;2;False) Namun Mnghasilkan Nilai #N/A :(

Reply
Anonymous
11 December, 2015 09:48 delete

gan,,mau nanya. misalkan saya punya cell A1:A10, outputnya pada cell A11. gimana caranya misalkan saya ketik angka 10 pada A1 maka pada A11 muncul angka 10 juga, pas pindah pada A2 saya ketik lagi angka 20, maka pada A11 muncul angka 20. mohon petunjuknya untuk kondisi rumusnya.terima kasih.

Reply
13 December, 2015 18:41 delete

Coba gunakan tanda & untuk menyatukan dua nama dan function LEFT RIGHT ditaruh di depan seperti contoh berikut:
=LEFT(VLOOKUP(A24;$A$17:$B$19;2;FALSE);4) & RIGHT(VLOOKUP(A24;$A$17:$B$19;2;FALSE);1)

Reply
13 December, 2015 19:09 delete

Coba rumus ini:
=LOOKUP(2,1/(1-ISBLANK(A1:A10)),A1:A10)


Formula ini akan mengambil data dari sel terakhir pada range A1:A10

Reply
Anonymous
09 January, 2016 13:26 delete

Haloo.. saya mohon bantuannya.
Saya mempunyai list daftar customer dan tagihan2 nya dimana list tersebut ada customer yang berulang2 melakukan pembelanjaan dan di sheet yang baru ini saya mau membuat rumus yang memunculkan hanya 1 customer saja dan tidak memunculkan berulang-ulang.
sebagai contoh saya mempunyai list 1000 nomor nota penjualan 150 customer. dan yang saya inginkan hanya memunculkan 150 customer tersebut (hanya 150 nomor bukan 1000 nomor. Mohon bantuannya. Terimakasih

Reply
25 July, 2016 20:45 delete

sama"
terima kasih telah berkunjung :)

Reply
16 August, 2016 10:12 delete

Om, gimana caranya mengatur lebar cell diatas yang berisi tulisan biasa tanpa ada tabel, sedangkan dibawahnya ada tabel dengan lebar cell yang berbeda. contohnya pada tulisan diatas tabel ada tanda tirik dua (:) yg membutuhkan lebar cell yang kecil. sedangkan dibawah (:) ada tabel yang lebarnya tidak bisa sama dengan lebar tanda (:).

Reply
16 August, 2016 11:31 delete

Untuk lebar cell yang lebih besar dari baris di atasnya, coba gabungakn 2 cell jadi 1 dengan merge cells.
Fitur ini ada di tab Home, bagian Alignment

Reply
29 September, 2016 12:06 delete

Mau nanya gan seumpama menggambil 1 huruf saja bagaimana gan?
Contoh "kamu" diambil huruf "a" saja atau "m" saja. Tolong bantuannya gan soalnya mau masukin sebuah nama tapi di masukin di dalam kolom satu kolom satu huruf

Reply
29 September, 2016 12:34 delete

Saya kurang mengerti dengan maksud ini > "masukin sebuah nama tapi di masukin di dalam kolom satu kolom satu huruf"

Tetapi bila Anda ingin mengambil 1 huruf dari bagian tengah saja,maka gunakan fungsi MID.

Reply
12 November, 2016 08:42 delete

Mohon bantuaanya (URGENT) jika di cell A2 adalah format DATE dengan tulisan 22-Jan-14. Saya ingin pada cell B2 muncul angka 1. Karna saya ingin jika di kolom A2 bulan Januari maka 1, dan begitu seterusnya sampai Desember maka 12. Mohon bantuannya bagaimana caranya ..

Reply
16 November, 2016 10:55 delete

Pada sel B2 masukkan formula =MONTH(A2)

Reply
17 November, 2016 10:18 delete

bang bisa bantuin buat rubah/ balik data text / angka dari kiri ke kanan

misal 12345 = 54321 / nama = aman


Reply