Từ ngày 15/09/2018 các nhà mạng tại Việt Nam đều phải đổi các đầu số từ 11 số về 10 số. Việc này trên điện thoại của bạn có rất nhiều phần mềm có thể hỗ trợ được. Nhưng với những danh sách điện thoại được lưu trữ trên Excel thì chúng ta làm thế nào? Hãy cùng Học Excel Online tìm hiểu chi tiết cách làm nhé.

Cách viết công thức trên Excel để chuyển số điện thoại từ 11 số về 10 số

Ví dụ chúng ta có bảng danh sách số điện thoại và bảng đầu số chuyển đổi như sau:

Các bạn có thể tải file mẫu Excel của bài viết tại địa chỉ: http://bit.ly/2P53V0c

Xử lý dữ liệu trong Bảng chuyển đổi đầu số

Đặc điểm của số điện thoại là có số 0 ở ký tự đầu tiên. Do đó thông thường số điện thoại ở dạng Text chứ không phải dạng Số (Number).

Trong bảng chuyển đổi đầu số E1:G22 chúng ta thấy cột Đầu số cũ và Chuyển đổi chứa dữ liệu dạng số và không có số 0. Do đó chúng ta có thể thêm số 0 vào phía trước các con số này như sau:

Tại ô I1 nhập số 0

Tại ô I2 nhập công thức =$I$1&F2

Lưu ý: Cố định ô I1 để không thay đổi giá trị này khi sao chép công thức cho các dòng khác, cột khác.

Tiếp theo chúng ta kéo công thức sao chép xuống dưới (fillDown) và sao chép sang phải (fillRight)

Như vậy chúng ta có thể thấy ở vùng I2:J22 các số đều có kèm theo số 0 ở trước.

Copy kết quả ở I2:J22 và dán trở lại vào vùng F2:G22, nhưng chỉ dán phần giá trị (Paste Special / Paste Value)

Sau khi xong bước này, chúng ta có thể xóa dữ liệu ở cột I và J được rồi. Chỉ cần thu được kết quả các đầu số có kèm số 0 phía trước là được. Khi đó dữ liệu trong bảng chuyển đổi đầu số trở thành dạng Text.

Xây dựng công thức chuyển đầu số

Trước hết chúng ta chỉ thực hiện chuyển đầu số cho các số có 11 chữ số. Vì vậy tại cột B – cột Số ký tự – chúng ta dùng hàm LEN để đo độ dài số ký tự của cột Số điện thoại

Tại cột C – Số Điện thoại mới – chúng ta sẽ bắt đầu thực hiện viết hàm IF để biện luận:

• logical_test: So sánh xem số ký tự ở cột B có lớn hơn 10 không
• value_if_true: Nếu kết quả là đúng (có 11 số) thì sẽ sử dụng hàm chuyển đổi đầu số
• value_if_false: Nếu kết quả là sai (chỉ có 10 số) thì sẽ giữ nguyên giá trị số điện thoại cũ.

Như vậy cấu trúc hàm IF tại ô C2 có thể viết là:

=IF(B2>10, chuyển đầu số của A2, A2)

Vậy Chuyển đầu số của A2 thực hiện thế nào?

Mỗi số điện thoại 11 số sẽ gồm 2 phần:

• Phần bên trái 4 chữ số: là đầu số cần chuyển đổi => Hàm LEFT, lấy 4 chữ số =LEFT(A2,4)
• Phần còn lại (7 chữ số): là phần giữ nguyên => Hàm RIGHT, lấy 7 chữ số =RIGHT(A2,7)

Khi chuyển đổi, chúng ta sẽ dò tìm xem kết quả thu được bởi hàm LEFT ở trên là vị trí nào trong Bảng chuyển đổi đầu số (F2:G2), dò trong cột F. Kết quả tương ứng với đầu số sẽ chuyển đổi là cột G (cột thứ 2 tính từ cột F) => Sử dụng hàm VLOOKUP, trong đó:

• lookup_value: là giá trị thu được bởi hàm LEFT, 4 chữ số
• table_array: vùng F2:G2, cố định bảng này.
• col_index_num: cột thứ 2
• range_lookup: tìm kiếm chính xác, chọn số 0

Ta có =VLOOKUP(LEFT(A2,4),$F$2:$G$2,2,0)

Do đó công thức đầy đủ tại ô C2 là:

Như vậy chúng ta đã hoàn thành được công thức chuyển đổi đầu số điện thoại trên Excel rồi. Ngoài cách sử dụng hàm VLOOKUP, các bạn có thể tham khảo cách sử dụng hàm INDEX, MATCH để chuyển đổi đầu số.

Tham khảo các bài viết:

Cách đổi số điện thoại từ 11 số sang 10 số trong Excel (dùng hàm INDEX+MATCH)

4 cách để nhập số điện thoại trong Excel mà không mất số 0

Cách giữ và xóa số 0 đầu dòng trong Excel