Tom làm việc với rất nhiều danh sách địa chỉ. Một điều khiến anh ta phát điên là khi địa chỉ đường phố nằm trong một ô duy nhất. Tom cần số nhà trong một ô và đường phố trong một ô khác. Vì vậy, nếu địa chỉ là “1234 Maple Glen Ave.” thì anh ấy cần “1234” trong một ô và “Maple Glen Ave.” trong cái khác. Anh ta có thể sao chép số nhà vào một cột đặt trước tên đường nhưng để gõ lại các số và sau đó loại bỏ chúng khỏi tên đường là một quá trình dài. Tom tự hỏi liệu có cách nào đơn giản hơn có thể “tách” số nhà khỏi tên đường không.

Có một số cách bạn có thể thực hiện nhiệm vụ này. Một số người có thể nghĩ rằng sử dụng công cụ Text to Columns (trên tab Data của ribbon)

sẽ cho kết quả mong muốn. Thật không may, đó không phải là cách tốt nhất để tách số nhà. Lý do rất đơn giản: Nếu bạn sử dụng công cụ bằng cách chỉ định dấu phân cách, thì dấu cách duy nhất có ý nghĩa là khoảng trắng. Nhưng nếu bạn làm điều đó, bạn sẽ kết thúc với “1234 Maple Glen Ave.” được trải rộng trên bốn cột thay vì hai cột mong muốn. Sau đó, bạn cần nghĩ ra một cách để dán tên đường phố lại với nhau.

Một cách khác để có thể sử dụng công cụ Văn bản thành Cột là với chiều rộng cột cố định (thay vì dấu phân cách). Cách tiếp cận này sẽ chỉ hoạt động nếu tất cả các địa chỉ của bạn có cùng số chữ số trong số nhà. Tất nhiên, trong hầu hết các tập dữ liệu, điều này sẽ không xảy ra — số nhà có thể là bất kỳ số chữ số nào và chúng thậm chí có thể bao gồm các ký tự không phải số (chẳng hạn như “1234A”, “1234-B” hoặc “1234-36”).

Một cách tiếp cận sẽ hiệu quả là sử dụng công thức để tách các địa chỉ ban đầu ra. Công thức của bạn có thể nhập vào khoảng trống đầu tiên trong địa chỉ, trả về phần ở bên trái khoảng trắng hoặc phần ở bên phải. Đây là cách để lấy ra mọi thứ trước khoảng trắng, được cung cấp một địa chỉ trong ô A1:

=LEFT(A1,FIND(" ",A1)-1)

Nếu bạn hoàn toàn chắc chắn rằng địa chỉ sẽ không chứa bất kỳ ký tự không phải số nào, bạn có thể bọc công thức trong hàm VALUE để bạn kết thúc bằng số nhà dưới dạng giá trị số:

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

Để lấy một phần của địa chỉ theo sau khoảng trắng đầu tiên, bạn có thể sử dụng công thức sau:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

Nếu thích, bạn có thể nghĩ ra một công thức tham chiếu đến số nhà mà bạn đã rút ra với công thức đầu tiên. Ví dụ, giả sử rằng công thức số nhà của bạn nằm trong cột B; bạn có thể đặt phần sau vào cột C:

=TRIM(SUBSTITUTE(A1,B1,))

Với các công thức của bạn trong cột B và C (và địa chỉ trong cột A), bạn có thể sao chép các công thức xuống cho bao nhiêu hàng cần thiết. Sau đó, bạn có thể chọn phạm vi B: C đó và sử dụng Dán Đặc biệt để dán các giá trị trở lại các ô đó. Sau khi thực hiện việc này, bạn có thể xóa các địa chỉ gốc trong cột A.

Nếu bạn thường xuyên phải thực hiện nhiều địa chỉ, bạn có thể được phục vụ tốt hơn khi sử dụng macro để thực hiện tách rời. Macro ví dụ sau hoạt động trên bất kỳ phạm vi nào bạn đã chọn khi chạy nó. Nó sẽ chèn một cột các ô trống ở bên trái vùng chọn, nhồi các ô đó với số nhà, sau đó điều chỉnh địa chỉ để số nhà không còn được đưa vào nữa.

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

Nếu muốn, bạn có thể tạo một hàm do người dùng định nghĩa chỉ trả về số nhà:

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

Một lợi thế khi sử dụng hàm do người dùng định nghĩa là nó sẽ kiểm tra xem phần đầu tiên của địa chỉ gốc có thực sự bắt đầu bằng một số hay không. Nếu không, thì nó giả định rằng địa chỉ không bắt đầu bằng số nhà. (Toàn bộ số nhà không cần phải là số; nó chỉ cần bắt đầu bằng một chữ số.)

_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 (13350) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.