Hướng dẫn từng bước để chuyển đổi số điện thoại 11 số thành 10 số sử dụng Excel một cách chi tiết

Kể từ 15/09/2018, tất cả các nhà mạng ở Việt Nam đã chuyển toàn bộ đầu số điện thoại từ 11 số sang 10 số. Trong khi trên smartphone có nhiều ứng dụng hỗ trợ việc này, còn đối với các danh bạ số điện thoại lưu trong Excel, chúng ta nên xử lý như thế nào? Hãy cùng khám phá cặn kẽ phương pháp này qua Siêu Marketing.

Các bước để viết công thức trên Excel nhằm thay đổi số điện thoại từ 11 số sang 10 số

Xét một 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 về mẫu file Excel cho bài học tại đường link: http://bit.ly/2P53V0c

Thông tin cần biết về bảng chuyển đổi đầu số

Số điện thoại thường bắt đầu bằng số 0, cho nên thường được nhập ở dạng Text chứ không phải Number. Trong bảng chuyển đổi đầu số từ ô E1 đến G22, ta thấy dữ liệu trong cột Đầu số cũChuyển đổi không chứa số 0 ở đầu. Vì thế, ta có thể thêm số 0 vào đằng trước như sau:

Gõ số 0 vào ô I1

Sau đó, vào ô I2 ta nhập công thức là =$I$1&F2

Chú ý: Khi kéo công thức cho những ô khác, nhớ cố định ô I1 để giá trị không thay đổi.

Kế đến, tiến hành kéo công thức về dưới (fillDown) và kéo ngang qua cột (fillRight)

Bây giờ, các bạn có thể quan sát thấy rằng số 0 đã được thêm vào trước các đầu số ở khu vực từ I2 đến J22.

Chép dữ liệu từ I2 đến J22 và dán giá trị trở lại vào vùng từ F2 đến G22, nhưng chỉ bằng cách dán giá trị chứ không dán toàn bộ (Copy & Paste Special / Paste Values)

Sau khi thực hiện bước trên, ta có thể xóa các dữ liệu trong cột I và J. Kết quả cuối cùng sẽ là các đầu số điện thoại đã được chuyển đổi dạng Text có số 0 phía trước.

Phát triển công thức để thay đổi đầu số

Đầu tiên ta chỉ chuyển đổi đầu số cho những số điện thoại đủ 11 chữ số. Tại cột B, cột Số ký tự, ta sử dụng hàm LEN để đếm số ký tự của cột Số điện thoại

Ở cột C – Số Điện thoại mới – bây giờ ta sẽ thực hiện hàm IF để xác định:

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

Vì vậy, cấu trúc của hàm IF ở ô C2 sẽ được viết như sau:

=IF(B2>10, thực hiện chuyển đổi đầu số cho A2, A2)

Cụ thể hơn, chuyển đổi đầu số cho A2 sẽ được diễn ra ra sao?

Mỗi số điện thoại 11 số có hai phần:

  • Phần bên trái là 4 chữ số đầu tiên cần được thay đổi => sử dụng hàm LEFT để lấy 4 số đầu =LEFT(A2,4)
  • Phần còn lại là 7 chữ số không đổi => dùng hàm RIGHT để lấy 7 số cuối =RIGHT(A2,7)

Trong quá trình chuyển đổi, chúng ta sẽ

Hãy thực hiện việc tìm kiếm để xác định vị trí mà kết quả từ hàm LEFT nêu trên tương ứng trong Bảng đổi đầu số, đó là cột F. Bảng này chứa thông tin đối chiếu ở cột G, tức là cột thứ hai tính từ cột F => Ta sẽ áp dụng hàm VLOOKUP với các thành phần sau:

  • lookup_value: giá trị do hàm LEFT trả về, bao gồm 4 số đầu tiên
  • table_array: khu vực $F$2:$G$2 được cố định lại.
  • col_index_num: đề cập đến cột số 2
  • range_lookup: chế độ tìm kiếm chính xác, lựa chọn giá trị 0

Phương trình sẽ như sau: =VLOOKUP(LEFT(A2,4),$F$2:$G$2,2,0)

Điều này dẫn chúng ta đến công thức cuối cùng đặt tại ô C2:

Chúng ta đã kết thúc công thức chuyển đổi đầu số điện thoại cho Excel. Không chỉ sử dụng VLOOKUP mà bạn cũng có thể tìm hiểu về việc dùng hàm INDEX và MATCH để đổi đầu số.

Hãy tham khảo thêm những bài viết dưới đây:

Cách để thay số điện thoại từ 11 số xuống còn 10 số trên Excel (áp dụng hàm INDEX+MATCH)

Có 4 phương pháp nhập số điện thoại vào Excel mà không làm mất đi số 0 ở đầu

Cách để giữ nguyên hoặc loại bỏ số 0 ở đầu mỗi dòng trong Excel

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop