Công thức chung
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Tóm lược
Một cách để dọn dẹp và định dạng lại số điện thoại là loại bỏ tất cả các ký tự không liên quan, sau đó áp dụng định dạng số điện thoại cài sẵn của Excel.
Công thức ở trên sử dụng một loạt các hàm SUBSTITUTE lồng nhau để loại bỏ khoảng trắng, dấu gạch nối, dấu chấm, dấu ngoặc đơn và dấu phẩy.
Bạn sẽ cần điều chỉnh các thay thế thực tế cho phù hợp với dữ liệu của mình.
Giải trình
Công thức chạy từ trong ra ngoài, với mỗi SUBSTITUTE xóa một ký tự.
SUBSTITUTE bên trong nhất loại bỏ các dấu ngoặc đơn bên trái và kết quả được chuyển cho SUBSTITUTE tiếp theo, loại bỏ các dấu ngoặc đơn bên phải, v.v.
Bất cứ khi nào bạn sử dụng hàm SUBSTITUTE, kết quả sẽ là văn bản. Vì bạn không thể áp dụng định dạng số cho văn bản, chúng tôi cần chuyển văn bản thành số. Một cách để làm điều đó là thêm số không (+0), tự động chuyển đổi số ở định dạng văn bản thành số ở định dạng số.
Cuối cùng, định dạng số điện thoại "Đặc biệt" được áp dụng (cột D).
Trang này giải thích các định dạng số tùy chỉnh và với nhiều ví dụ.
Thủ thuật khoảng trắng để dễ đọc hơn
Khi lồng nhiều hàm, có thể khó đọc công thức và giữ cân bằng tất cả các dấu ngoặc đơn. Excel không quan tâm đến khoảng trắng thừa trong công thức, vì vậy bạn có thể thêm dấu ngắt dòng trong công thức để làm cho công thức dễ đọc hơn. Ví dụ, công thức trên có thể được viết như sau:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Lưu ý rằng ô xuất hiện ở giữa, với các tên chức năng ở trên và các thay thế bên dưới. Điều này không chỉ làm cho công thức dễ đọc hơn mà còn giúp dễ dàng thêm và xóa các thay thế.
Bạn cũng có thể sử dụng thủ thuật này để làm cho các câu lệnh IF lồng nhau dễ đọc hơn.
