Mengextract angka dari cells yang berisi text dan angka

Misalnya kita punya worksheet yang berisi sbb:

  • abcd98765efghi
  • 1250chances
  • red 1000chances
  • Blue30457 56chances
  • White222222

Untuk mengextract angka dari cells kita bisa pakai cara seperti pada artikel sebelumnya (Men-Split/Memecah Kalimat/Value untuk mengambil nilai tertentu) namun untuk data diatas tidak ada pengenal/karakter unik yang bisa jadi penanda untuk posisi awal pengambilan angka

Salah satu cara yang bisa kita pakai adalah menggunakan array formula (baca Pengenalan array formula untuk mengetahui definisi array formula)

Konsep awal :

  1. Cari posisi awal dari angka, disini kita gunakan search dengan array sebagai argumentnya yang juga akan menghasilkan array, caranya dengan mencari posisi dari angka 0,1,...,9 pada tiap cells
  2. Lalu kita cari hasilnya dengan fungsi MIN untuk mengetahui angka terendah
  3. Lalu kita Ambil dari cells tersebut mulai dari posisi terendah tadi, yang pasti dimulai dengan angka (0 s.d 9), Kita pakai rumus Mid dng argumen text, posisi awal dan array panjang text yang mo diambil (fungsi mid ini juga akan menghasilkan array)
  4. Kita cari dari hasil array tadi yang bernilai paling besar dengan fungsi Lookup
  5. Ketemu angka yang dimaksud

Langkah 1

Text yang dicari ada di B1 , yaitu "abcd98765efghi"

rumusnya

=SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789")

ini akan mencari apakah text 0,1,2,...,9 ada di posisi mana pada text yang dicari

rumus ini karena argumennya array yaitu text yang dicari ({"0","1","2","3","4","5","6","7","8","9"}) sebanyak 10 maka akan menghasilkan array juga sebanyak 10 yaitu {15, 16, 17, 18, 19, 9, 8, 7, 6, 5}

ini artinya angka 0 ketemu diposisi 15, angka 1 diposisi 16 (karena lebih dari 14/panjang text, maka berarti tidak ada di text)

Posisi yang paling kecil berarti posisi yang paling depan, ini adalah awal dari suatu angka, yaitu posisi 5 berisi 9

Langkah 2

Kita cari posisi terkecil dengan fungsi Min

Rumusnya

=Min(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789"))

akan ketemu angka 5

Langkah 3

Kita akan potong dengan mid, dengan posisi awal adalah 5, panjang kita buat array karena kita tidak tahu berapa panjang dari angka tersebut, jadi kita isi dengan array {1,2,3,....,15)

kita pakai angka 15 sebagai karena angka yang mutlak aja, kita asumsikan angkanya tidak lebih besar dari 999 trilyun tapi itu terserah kita juga

Kita juga bisa pakai panjang dari text sebagai angka terpanjang

Untuk membuat array horisontal berisi angka 1..15 kita pakai fungsi

=Column($1:$15)

Kita pakai absolut agar kalau rumus dikopi tidak berubah

fungsi ini akan menghasilkan {1,2,..,15}

maka lengkapnya fungsi untuk mengambil text tersebut :

=MID(B1,MIN(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789")),COLUMN(1:15))

akan menghasilkan

{9, 98, 987, 9876, 98765, 98765e, 98765ef, 98765efg, 98765efgh, 98765efghi, 98765efghi, 98765efghi, 98765efghi, 98765efghi, 98765efghi}

Langkah 4

Kita ubah text tadi menjadi angka dengan fungsi value atau tanda -- (artinya negatif*negatif=positif) yang akan mengubah text menjadi angka

=--(MID(B1,MIN(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789")),COLUMN(1:15)))

yang akan menghasilkan
{9, 98, 987, 9876, 98765, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}

Fungsi ini akan menghasilkan error jika angka tidak terdapat didalamya (semua berisi text karena akan menghasilkan blank yang jika kena rumus value akan error) , oleh karena itu kita ubah dengan menambahkan angka 0

=--("0" & MID(B1,MIN(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789")),COLUMN(1:15)))

Langkah 5

lalu hasil array diatas akan dilookup dengan mencari nilai yang paling besar

=LOOKUP(99^99,--("0" & MID(B1,MIN(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, B1&"0123456789")),COLUMN(1:15))))

Perlu diingat bahwa lookup akan mencari nilai yang paling mendekati tapi tidak melebihi

jadi kita akan mencari di array tadi angka yang paling mendekati 99^99 (99 pangkat 99) yang tentu saja akan ketemu angka yang paling besar yaitu 98765

Selesai!!!

Kalau bingung tolong Comment

ini spreadsheetnya

File Extract Number.xlsx bisa didonlot disini

Sumber : http://www.microsoft-studios.com/2012/10/mengextract-angka-dari-cells-yang-berisi-text-dan-angka/

Tagged . Bookmark the permalink.

One Response to Mengextract angka dari cells yang berisi text dan angka

  1. badjoeadjie says:

    angka mutlak pada rumus
    Column($1:$15)
    bisa diganti dengan
    Column(indirect(“$1:$” & len(B1))

    jika B1=abcd98765efghi
    len(b1)=14

    maka akan menghasilkan
    column(indirect(“$1:$14”))

    atau sama dengan
    Column($1:$14)

    akan menghasilkan {1,2,3,…,14}

Leave a Reply

Your email address will not be published. Required fields are marked *