Ron có một trang tính có một cột số điện thoại. Một số là các số có bảy chữ số, như trong 123-4567, với mã vùng 727 được giả định.

Những người khác có mã vùng, như trong (890) 123-4567. Ron muốn có mọi số điện thoại ở định dạng sau và tự hỏi về cách tốt nhất để phát hiện các số điện thoại ngắn hơn và thêm số bị thiếu (727) vào các số đó.

Cách tốt nhất để thực hiện điều này là phụ thuộc vào bản chất dữ liệu của bạn. Bạn thấy đấy, dữ liệu có thể là văn bản hoặc số. Điều này có thể thực hiện được vì Excel cung cấp định dạng ô đặc biệt cho phép các số như 8015551212 được hiển thị dưới dạng (801) 555-1212. Bạn có thể biết liệu dữ liệu của mình là số hay văn bản bằng cách so sánh những gì bạn thấy trong ô với những gì được hiển thị trong thanh công thức. (Xem Hình 1.)

image

Hình 1. Tìm hiểu xem một số điện thoại là văn bản hay số.

Nếu dữ liệu của bạn là số, bạn có thể dễ dàng thêm mã vùng thích hợp bằng cách sử dụng công thức như sau:

=IF(A1<=9999999,7270000000+A1, A1)

Kết quả sẽ thêm mã vùng thích hợp, nhưng bạn vẫn cần định dạng ô chứa công thức này với định dạng Số điện thoại đặc biệt.

Nếu dữ liệu là dạng văn bản, thì bạn có thể chỉ cần sử dụng công thức để kiểm tra độ dài của ô và sau đó thêm mã vùng, nếu cần:

=IF(LEN(A1) = 8, "(727) " & A1, A1)

Do đó, nếu số điện thoại là 555-1212 (dài 8 ký tự), thì công thức này trả về (727) 555-1212. Tất nhiên, có thể số điện thoại có thể có một số dấu cách thừa xung quanh nó; điều này thường xảy ra nếu số điện thoại được dán vào trang tính từ một nguồn khác. Trong trường hợp đó, chỉ cần đưa hàm TRIM vào công thức của bạn:

=IF(LEN(TRIM(A1)) = 8, "(727) " & TRIM(A1), TRIM(A1))

Có một công thức khác mà bạn có thể thấy rất hữu ích để thêm mã vùng và nó ngắn hơn những công thức chúng ta đã thấy cho đến nay:

=RIGHT("(727) " & TRIM(A1), 14)

Công thức này thêm mã vùng vào tất cả các ô và sau đó chỉ lấy 14 ký tự ngoài cùng bên phải của kết quả.

Mọi thứ trở nên khó khăn hơn một chút nếu dữ liệu của bạn có một số số điện thoại là số và một số ở dạng văn bản. Trong trường hợp đó, bạn vẫn có thể đặt lại một công thức, nhưng nó sẽ dài hơn:

=IF(LEN(TRIM(A1)) > 9, TRIM(A1), IF(LEN(TRIM(A1)) = 8, "(724) " & TRIM(A1), IF(LEN(TRIM(A1)) = 7, 7240000000+A1, "Undetermined")))

Hãy nhớ rằng đây là một công thức duy nhất. Nó khóa độ dài của bất kỳ thứ gì trong ô A1. Nếu độ dài trên 9 ký tự, thì số điện thoại được giả định là chính xác. Nếu độ dài chính xác là 8, thì công thức sẽ giả định rằng đó là phiên bản “văn bản ngắn” của số điện thoại, chẳng hạn như 555-1212 và thêm mã vùng vào đầu. Nếu độ dài chính xác là 7, thì nó được giả định là một số điện thoại “số ngắn” và số thích hợp được thêm vào đó. Nếu số điện thoại không phải là bất kỳ số nào trong số này, thì công thức trả về văn bản “Không xác định”. Sau khi sử dụng công thức này, bạn vẫn cần định dạng các ô kết quả bằng định dạng Số điện thoại đặc biệt.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13564) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.