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

Chia sẻ bài viết này:
  • 1
  •  
  •  
  •  
  •  
  •  
  •   
  •   

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ũ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)

Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.

Với khoá học này học viên sẽ nắm được: :

  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

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


Chia sẻ bài viết này:
  • 1
  •  
  •  
  •  
  •  
  •  
  •   
  •