Allan có một danh sách hàng trăm tên và địa chỉ. Các địa chỉ đường phố nằm trong khoảng từ Main Street, 123 Main Street, US RT 2, hoặc 187 South Elm St. Anh ấy muốn phân biệt số đường từ các địa chỉ.

Vì vậy, địa chỉ 123 Main Street sẽ kết thúc bằng “123” trong một ô và “Main Street” trong ô khác. Nếu không có số đường phố, thì cột số đường phố sẽ không có gì. Công cụ Text to Columns sẽ không hoạt động và anh ấy tự hỏi làm thế nào anh ấy có thể làm điều này.

Trong một thế giới hoàn hảo, Excel sẽ cho phép bạn dễ dàng tách các số ra khỏi tên đường. Vì tùy chọn này không tồn tại, bạn có một số lựa chọn. Tùy chọn tốn thời gian nhất liên quan đến việc thêm một cột bổ sung và nhập lại dữ liệu. Tuy nhiên, nếu bạn muốn tiết kiệm thời gian, bạn có thể sử dụng nhiều công thức khác nhau để hoàn thành nhiệm vụ.

Giả sử danh sách địa chỉ nằm trong cột A (bắt đầu bằng ô A1), bạn có thể sử dụng công thức tương tự như sau để lấy ra phần số của địa chỉ:

=IF(ISERROR(VALUE(LEFT(A1,1))),"",LEFT(A1,FIND(" ",A1)-1))

Giả sử bạn đặt công thức vào ô B1, sau đó bạn có thể sử dụng một công thức khác để lấy phần không phải số của địa chỉ:

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

Lưu ý rằng cách tiếp cận này có một giới hạn. Một số địa chỉ, đặc biệt là ở các khu vực đô thị lớn, sử dụng định dạng chẳng hạn như 152-33 Bell Blvd. Các công thức trên sẽ hoạt động cho các địa chỉ này, nhưng nếu sử dụng phương án thay thế, 152 33 Bell Blvd., thì công thức sẽ phân tích cú pháp không chính xác. Trừ khi bạn muốn mua một chương trình phân tích cú pháp địa chỉ được phát triển chuyên nghiệp, các công thức trên và quét nhãn cầu nhanh kết quả phải là đủ.

Một công thức khác hoạt động trong trường hợp này. Giả sử địa chỉ của bạn nằm trong ô A2, hãy nhập công thức sau vào ô B2:

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

Công thức này cho biết, “Nếu ký tự đầu tiên không phải là số, hãy để trống ô. Nếu không, hãy cung cấp cho tôi tất cả các ký tự ở bên trái, nhưng không bao gồm, khoảng trống đầu tiên.” Sau đó, bạn có thể sử dụng kết quả của công thức này để lấy ra phần không phải số của địa chỉ:

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

Một cách tiếp cận khác là sử dụng công thức mảng. Ở đây một lần nữa, giả sử địa chỉ của bạn nằm trong ô A2, bạn có thể sử dụng như sau:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE,LEFT(A2,FIND(" ",A2,1)),"")

Vì đây là công thức mảng nên bạn cần nhập nó bằng cách sử dụng Ctrl + Shift + Enter. Kết quả là công thức trả về phần số đứng đầu của địa chỉ. Sau đó, bạn có thể xác định phần không phải số bằng cách sử dụng công thức mảng sau:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

Cuối cùng, macro sau đây có thể được sử dụng để tách địa chỉ đường phố khỏi tên đường.

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

Để sử dụng macro này, chỉ cần chọn phạm vi ô chứa địa chỉ của bạn và sau đó chạy nó. Phần số đứng đầu của địa chỉ sẽ xuất hiện trong ô bên phải của mỗi địa chỉ và số dư của địa chỉ sẽ được đặt trong ô bên phải của địa chỉ đó. (Vì vậy, bạn nên đảm bảo rằng có hai cột trống ở bên phải địa chỉ bạn chọn.)

_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 (8031) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: