Hướng dẫn cách chuyển đổi số điện thoại từ 11 số thành 10 số trên Excel

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

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

Giả sử bạn có một bảng danh sách số điện thoại và bảng đầu số chuyển đổi như sau:

Danh sách số điện thoại

Bạn có thể tải file Excel mẫu của bài viết tại đây.

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. Thông thường, số điện thoại được lưu dưới dạng Text thay vì dạng Số (Number).

Trong bảng chuyển đổi đầu số, các 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ần 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

Thêm số 0 trước các đầu số

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

Tiếp theo, kéo công thức đã tạo xuống dưới (fillDown) và sang phải (fillRight).

Fill công thức xử lý đầu số

Như vậy, ở vùng I2:J22, tất cả các số đã có số 0 ở trước.

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

Sau khi hoàn thành bước này, bạn có thể xóa dữ liệu ở cột I và J. Chỉ cần thu được kết quả các đầu số có 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 (Số ký tự), chúng ta sử 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ẽ sử dụng hàm IF để thực hiện chuyển đổi:

  • logical_test: So sánh 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ố), sử dụng hàm chuyển đổi đầu số
  • value_if_false: Nếu kết quả là sai (chỉ có 10 số), giữ nguyên giá trị số điện thoại cũ.

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” được thực hiện như 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 – sử dụng 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 – sử dụng hàm RIGHT, lấy 7 chữ số =RIGHT(A2,7)

Khi chuyển đổi, chúng ta sẽ tìm kết quả thu được từ hàm LEFT trong vùng Bảng chuyển đổi đầu số (F2:G22), tìm 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, với các tham số:

  • lookup_value: là giá trị thu được từ hàm LEFT (4 chữ số)
  • table_array: vùng F2:G22, 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

Vậy, công thức tại ô C2 là:

=VLOOKUP(LEFT(A2,4),$F$2:$G$22,2,0)

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

Kết quả chuyển đổi đầu số

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

Đừng quên tham khảo các bài viết liên quan: