Formula generik
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Ringkasan
Salah satu cara untuk membersihkan dan memformat semula nombor telefon adalah dengan melucutkan semua watak asing, kemudian menerapkan format nombor telefon bawaan Excel.
Formula di atas menggunakan rangkaian fungsi SUBSTITUTE bersarang untuk menghilangkan ruang, tanda hubung, noktah, tanda kurung, dan koma.
Anda perlu menyesuaikan penggantian sebenar agar sesuai dengan data anda.
Penjelasan
Rumusnya berjalan dari dalam ke luar, dengan setiap SUBSTITUT menghapus satu watak.
SUBSTITUT paling dalam menghilangkan tanda kurung kiri, dan hasilnya diserahkan kepada SUBSTITUT berikutnya, yang menghilangkan tanda kurung kanan, dan seterusnya.
Setiap kali anda menggunakan fungsi SUBSTITUTE, hasilnya akan berupa teks. Kerana anda tidak dapat menerapkan format angka ke teks, kami perlu mengubah teks menjadi angka. Salah satu cara untuk melakukannya adalah dengan menambahkan sifar (+0), yang secara automatik menukar nombor dalam format teks menjadi angka dalam format angka.
Akhirnya, format nombor telefon "Khas" digunakan (lajur D).
Halaman ini menerangkan format nombor tersuai dan dengan banyak contoh.
Trik ruang putih untuk lebih mudah dibaca
Semasa meletakkan pelbagai fungsi, sukar untuk membaca formula dan memastikan semua kurungan seimbang. Excel tidak mementingkan ruang kosong tambahan dalam formula, jadi anda boleh menambahkan jeda baris dalam formula untuk menjadikan formula lebih mudah dibaca. Contohnya, formula di atas boleh ditulis seperti berikut:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Perhatikan bahawa sel muncul di tengah, dengan nama fungsi di atas dan pengganti di bawah. Ini bukan sahaja menjadikan formula lebih mudah dibaca, tetapi juga memudahkan untuk menambah dan membuang pengganti.
Anda boleh menggunakan helah yang sama untuk membuat penyataan IF bersarang lebih mudah dibaca juga.
