Dan có một cột ô và mỗi ô chứa ba mục: thành phố, tiểu bang và Mã ZIP. (Cả ba đều nằm trong một ô, giống như bạn thấy trong một dòng địa chỉ.) Một số Mã ZIP có năm chữ số và một số là chín. Dan cần kéo cả trạng thái hai ký tự và mã ZIP gồm năm chữ số vào các ô của riêng chúng, ở bên phải dữ liệu hiện tại. Dan biết anh ấy có thể sử dụng công cụ Text to Columns, nhưng cảm thấy rằng nó liên quan đến rất nhiều công việc vì anh ấy sẽ cần phải xử lý các tên thành phố nhiều từ và dấu phẩy. Dan không thể không nghĩ rằng có thể có một cách tiếp cận công thức sẽ dễ dàng hơn.

Cần có một vài giả định về dữ liệu để đưa ra bất kỳ khuyến nghị nào. Ví dụ, hãy giả sử rằng tất cả dữ liệu ở định dạng này:

My Town, CA 98765-4321

Phần từ dấu gạch ngang trở đi (phần cuối của Mã ZIP) là tùy chọn, nhưng vị trí của dấu phẩy là tĩnh — nó luôn theo sau tên của thị trấn — và tiểu bang luôn bao gồm hai ký tự. Trong trường hợp này, thật dễ dàng để tạo ra hai công thức trích xuất từ ​​viết tắt của tiểu bang và năm chữ số đầu tiên của Mã ZIP:

=MID(A1,FIND(",",A1)+2,2)

=MID(A1,FIND(",",A1)+5,5)

Cả hai công thức đều nhập vào dấu phẩy; nó phục vụ như một dấu phân cách giữa thành phố và hai mục thực sự muốn. Nếu không có dấu phẩy trong dữ liệu hoặc nếu có nhiều dấu phẩy, thì công thức sẽ không trả về thông tin mong muốn.

Các công thức cũng giả định rằng không có khoảng trắng thừa nào trong dữ liệu của bạn; tối đa chỉ có một khoảng trắng sau dấu phẩy và giữa trạng thái và Mã ZIP. Tất nhiên, điều này đủ dễ dàng để thực thi — chỉ cần sử dụng Tìm và Thay thế để thay thế hai khoảng trắng bằng một khoảng trắng ở bất kỳ đâu trong trang tính của bạn.

Tất nhiên, nếu dữ liệu của bạn có cấu trúc này, bạn vẫn có thể dựa vào công cụ Văn bản thành Cột để thực hiện công việc của mình. Tất cả những gì bạn cần làm là chạy công cụ và phân chia dữ liệu của bạn dựa trên dấu phẩy. Thao tác này sẽ để thành phố trong một ô và đặt tiểu bang và Mã ZIP vào ô tiếp theo. Sau đó, bạn có thể sử dụng Văn bản thành Cột một lần nữa, lần này trên ô thứ hai (không phải tên thành phố) và phân chia nội dung dựa trên khoảng trắng.

Nếu dữ liệu của bạn không có cấu trúc như vậy — có lẽ dữ liệu có nhiều dấu phẩy trong địa chỉ hoặc dấu cách thừa — thì một cách tiếp cận hoàn toàn khác được yêu cầu. Để giải quyết vấn đề này, kỹ thuật cơ bản bao gồm việc cắt xén dữ liệu để loại bỏ các khoảng trắng không liên quan (đầu, cuối và trong), sau đó xác định vị trí của khoảng trắng cuối cùng và khoảng trắng cuối cùng.

Bạn có thể lấy ra năm chữ số trong Mã ZIP, được định nghĩa là ngay sau khoảng trống cuối cùng trong dữ liệu, bằng cách sử dụng công thức sau:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

Viết tắt trạng thái gồm hai ký tự có thể được trả lại bằng cách kéo hai ký tự ra ngay sau dấu cách từ thứ hai đến cuối cùng:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

Nếu dữ liệu của bạn thậm chí còn ít cấu trúc hơn — có lẽ nó bao gồm các địa chỉ không phải tất cả đều có chữ viết tắt trạng thái hai ký tự (N.J. thay vì NJ) —thì tốt nhất bạn nên sử dụng macro để phân chia dữ liệu.

Lý do cho điều này là VBA có một tập hợp các hàm xử lý văn bản phong phú hơn nhiều so với những gì bạn có thể làm bằng công thức Excel. Macro sau tạo một hàm do người dùng định nghĩa có thể trả về trạng thái hoặc Mã ZIP:

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String     Dim J As Integer     Dim K As Integer

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function

sState = "?"

sZIP = "?"

For J = Len(rstrAddress) To 1 Step -1         If Mid(rstrAddress, J, 1) = " " And sZIP = "?" Then             sZIP = Mid(rstrAddress, J + 1, 5)

rstrAddress = Trim(Left(rstrAddress, J))

For K = Len(rstrAddress) To 1 Step -1                 If Mid(rstrAddress, K, 1) = " " And sState = "?" Then                     sState = Mid(rstrAddress, K + 1, 20)

rstrAddress = Trim(Left(rstrAddress, K))

End If             Next K         End If     Next J     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

Để sử dụng chức năng này, chỉ cần cung cấp tham chiếu ô và 1 (nếu bạn muốn trạng thái) hoặc 2 (nếu bạn muốn Mã ZIP). Đây là một ví dụ về yêu cầu Mã ZIP cho bất kỳ địa chỉ nào nằm trong ô A1:

=GetStateZIP(A1,2)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

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

Mẹo này (9598) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: