Formula generik
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Ringkasan
Untuk memisahkan nombor dari nilai unit, anda boleh menggunakan formula berdasarkan beberapa fungsi: MAX, ISNUMBER, NILAI, dan MID. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Catatan: ini adalah formula eksperimen yang menggunakan pemalar susunan berkod keras, yang dinyatakan di sini untuk rujukan dan komen. Diuji secara santai sahaja, jadi berhati-hati jika anda menggunakan atau menyesuaikan diri.
Penjelasan
Kadang kala anda menemui data yang mencampurkan unit secara langsung dengan nombor (iaitu 8km, 12v, 7.5hrs). Malangnya, Excel akan menganggap nombor dalam format ini sebagai teks, dan anda tidak akan dapat melakukan operasi matematik pada nilai tersebut.
Untuk memisahkan nombor dari nilai unit, anda perlu menentukan kedudukan nombor terakhir. Sekiranya anda menambah 1 pada kedudukan itu, anda mempunyai permulaan teks unit. Rumus ini menggunakan konsep ini untuk mengetahui di mana unit ukuran bermula.
Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Formula ini menggunakan fungsi MID untuk mengekstrak 9 nilai pertama dalam B5, satu watak pada satu masa. Hasilnya adalah susunan seperti ini:
("8","0","v","","","","","","")
Kami kemudian menggunakan fungsi NILAI untuk menukar nombor dalam format teks menjadi nombor sebenar. Hasilnya adalah:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Kami menjalankan array ini melalui ISNUMBER untuk mendapatkan:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Kemudian gandakan kali ganda array lain dengan 9 nombor untuk mendapatkan:
(1,2,0,0,0,0,0,0,0)
Kemudian kami menggunakan MAX untuk mendapatkan nilai terbesar, iaitu kedudukan "nombor terakhir".
Akhirnya, kita menambah 1 pada kedudukan untuk mendapatkan kedudukan "unit start".
Akhirnya, kami menggunakan kedudukan ini dengan fungsi KIRI dan KANAN standard untuk memisahkan nombor dari unit:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Perhatikan bahawa pemalar array nombor yang dikodkan keras adalah peretasan untuk kemudahan, dan hanya akan menangani nilai mentah hingga 9 aksara.
