25 September 2014

Memisahkan Data Alamat

Salah satu yang saya sukai di rumus excel adalah manipulasi data teks, nah kali ini yang di utak atik adalah data alamat.








Coba perhatikan data alamat diatas, ya disana data dipisahkan oleh koma dan ada jarak spasi disana, itu yang menjadi kunci untuk rumusnya nanti.

Hasil yang di-inginkan








Yang dipisahkan adalah alamat, kemudian kota, propinsi dan terakhir adalah kode pos. Mohon diingat jika ingin memisahkan seperti ini, anda harus punya data yang polanya konsisten, karena rumus excel tidak akan mengenal "Sutrabaya" sebagai kota atau "Jawa Tengah" sebagai propinsi, yang penting polanya sama sehingga logika pemisahannya bisa diterapkan pada rumus.

Pertama-tama yang harus dilakukan adalah mengambil data "Kota", "Propinsi" dan "Kode Pos", karena ini sebagai kunci.








Rumusnya adalah:
=TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270))    , dari sini yang lain jadi agak mudah.

Rumus Kota:
=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));SEARCH(" ";TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));1)-1))

Rumus Alamat:
=MID(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1));1;LEN(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1)))-1)

Rumus Propinsi:
=TRIM(MID(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2;", ";REPT(" ";90));270));" ";REPT(" ";99));100;200))

Rumus Kode Pos:
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))

Nah mungkin ada dari pembaca yang punya rumus lain? silahkan di-share disini

Salam
Rizky

Download File Latihan

4 komentar:

  1. Maaf numpang nanya admin.
    Saya punya problem mencari formula untuk:
    Logika: Jika pada kolom A5 s/d F5 terdapat nilai 6 atau 7 atau 8, maka pd cell G5 otomatis berisi "CUKUP", selain nilai tersebut maka cell G5 kosong (blank).
    Makasih sebelumnya.

    BalasHapus
    Balasan
    1. Letakkan di G5:

      =IF(COUNT(FIND({6;7;8};A5:F5))>0;"Cukup";"")

      kemudian tekan tombol CTRL+SHIFT+ENTER bersamaan, karena ini rumus array

      Hapus
  2. Dear admin untuk pencarian alamat tersebut kolom cell d2 itu apa ya

    BalasHapus
  3. Simpang Jengkol Rt. 12 Rw. 8 Kel. Kasang Bangsawan Kec. Pujud Kabupaten Rokan Hilir
    saya mau misahkan kel. kec. dan kabupaten mohon pencerahan bro

    BalasHapus