Hướng dẫn 5 cách tách địa chỉ trong excel nhanh chóng, đơn giản

Trong quá trình làm việc với Excel thì bạn sẽ gặp danh sách dữ liệu chứa địa chỉ của khách hàng như: số nhà, phường, quận, thành phố… và bạn đang muốn tách riêng từng địa chỉ nhưng chưa biết cách làm thế nào. Đừng lo vì trong bài viết hôm nay, Unica sẽ hướng dẫn cho các bạn cách tách địa chỉ trong excel nhanh chóng và đơn giản nhé.

Khi nào cần tách địa chỉ trong Excel

Cách tách địa chỉ xã huyện tỉnh trong excel sẽ được sử dụng trong một số trường hợp sau:

  • Thực hiện yêu cầu thống kê báo cáo thông tin theo địa chỉ.

  • Thực hiện phân loại địa chỉ theo điều kiện đi kèm.

  • Thực hiện đếm dữ liệu theo địa chỉ và điều kiện đi kèm (nếu có)

Cách tách địa chỉ trong excel

Cách tách địa chỉ trong excel

  • Thực hiện phân nhóm theo địa chỉ và điều kiện đi kèm (nếu có)
  • Dùng địa chỉ làm điều kiện tra cứu dữ liệu theo yêu cầu. 

>>> Xem thêm: Autofill excel là gì? Cách dùng Autofill điền dữ liệu tự động

Cách để tách địa chỉ trong Excel 

Cách lọc địa chỉ trong excel sẽ được thực hiện theo 1 trong 5 cách sau đây, bạn hãy tham khảo để biết cách thực hiện cho mình trong quá trình làm việc nhé.

excel

Cách tách địa chỉ trong Excel bằng hàm SEARCH

Chúng ta có bảng dữ liệu để tách phường xã trong excel như sau:

Bảng dữ liệu cho trước 

Bảng dữ liệu cho trước 

Yêu cầu: Hãy dùng hàm Search để tách lấy tên Phường trong mục “Địa chỉ”

Cách thực hiện như sau:

Bước 1: Dùng hàm Search để xác định vị trí ký tự cần lấy đầu tiên (gọi là giá trị X) và vị trí ký tự cần lấy cuối cùng ( gọi là giá trị Y) trong dữ liệu sẵn có.

Chúng ta sẽ dùng hàm Search để xác định giá trị X,Y theo công thức sau:

X=SEARCH(“, P”,B2)

Áp dụng công thức hàm Search để xác định giá trị X

Áp dụng công thức hàm Search để xác định giá trị X

Y=SEARCH(“, Q”,B2)

Áp dụng công thức hàm Search để xác định giá trị Y

Áp dụng công thức hàm Search để xác định giá trị Y

Kết quả nhận được là X=21 và Y=41

Kết quả nhận được

Kết quả nhận được

>>> Tất cả đều có trong cuốn sách “HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO”

ĐĂNG KÝ MUA NGAY

Bước 2: Xác định công thức tách tên “Phường” trong địa chỉ.

Trong cách tách địa chỉ trong excel, hàm search chỉ có tác dụng xác định vị trí ký tự chúng ta cần tách. Để đưa ra kết quả chính xác theo yêu cầu, chúng ta cần kết hợp với hàm MID để lấy giá trị ở giữa chuỗi địa chỉ. 

Công thức áp dụng cụ thể như sau:

Ở đây chúng ta có 2 trường hợp: 

Trường hợp 1: nếu chuỗi ký tự bạn muốn lấy trả về kết quả “P. Bình Trưng Tây” thì bạn hãy áp dụng công thức sau: =MID(B2,C2+2,(D2-C2-2))

Trong đó:

  • B2: là dữ liệu địa chỉ gốc.

  • C2+2: là ký tự bắt đầu lấy trong C2. Ở đây ký tự đầu tiên chúng ta lấy sẽ là “P”. Có nghĩa là vị trí chữ P sẽ là vị trí của “, P”+2.

  • (D2-C2-2): là số ký tự chúng ta sẽ lấy trong C2.

Công thức tách tên

Công thức tách tên “phường” trong địa chỉ

Nhấn Enter để nhận kết quả

Kết quả nhận được

Kết quả nhận được

Trường hợp 2: Nếu chuỗi ký tự bạn muốn lấy trả về kết quả “Bình Trưng Tây” thì bạn hãy áp dụng công thức sau:

=MID(B2,C2+5,(D2-C2-5))

Trong đó:

  • B2: là dữ liệu địa chỉ gốc.
  • C2+5: là ký tự bắt đầu lấy trong C2. 
  • (D2-C2-5): là số ký tự chúng ta sẽ lấy trong C2.

Công thức tách tên phường trường hợp 2

Công thức tách tên phường trường hợp 2

Nhấn Enter để nhận kết quả

Kết quả tách tên phường trường hợp 2

Kết quả tách tên phường trường hợp 2

>>> Xem thêm: Gợi ý cách dùng địa chỉ tuyệt đối và địa chỉ tương đối trong Excel

Cách tách dữ liệu trong excel bằng hàm RIGHT

Chúng ta có bảng dữ liệu

Ví dụ cho trước một bảng dữ liệu

Ví dụ cho trước một bảng dữ liệu

Yêu cầu: Hãy dùng hàm RIGHT để tách lấy “xã/phường, quận/huyện, tỉnh/thành phố” trong ô “Địa chỉ”

Cách thực hiện như sau:

Bước 1: Chúng ta cần phân tích yêu cầu bảng tính.

Theo yêu cầu, nội dung chúng ta cần lấy bắt đầu là “P.” trở về sau. Lúc này chúng ta cần xác định vị trí của “P.” trong ô địa chỉ. Chúng ta gọi đây là giá trị X

Bước 2: Tìm giá trị X (Tổng số ký tự chúng ta sẽ lấy theo yêu cầu)

Để tìm giá trị X chúng ta sẽ áp dụng công thức sau: =SEARCH(“P. “,B2)

Nhập công thức để tìm giá trị X

Nhập công thức để tìm giá trị X

Và đây là kết quả khi nhấn Enter

Kết quả khi nhấn enter

Kết quả khi nhấn enter

Bước 3: Dùng hàm Right để tách lấy địa chỉ theo yêu cầu của bảng tính.

Chúng ta sẽ áp dụng công thức cụ thể sau đây: =RIGHT(B2,LEN(B2)-(C2-1)) 

Trong đó:

  • B2: là địa chỉ gốc
  • Len(B2:) là tổng số ký tự có trong B2
  • (C2-1): là tổng số ký tự bạn muốn lấy tính từ phải qua trái trong ô B2

Sử dụng hàm Right để lấy địa chỉ theo yêu cầu

Sử dụng hàm Right để lấy địa chỉ theo yêu cầu

Bước 4: Nhấn Enter để nhận kết quả

Nhấn enter để nhận kết quả hàm Right

Nhấn enter để nhận kết quả hàm Right

Cách tách địa chỉ trong Excel bằng tính năng Text To Column

Tính năng Text To Column trong excel sẽ giúp bạn tách từng phần xã/phường, quận/huyện, tỉnh/thành phố trong ô địa chỉ một cách nhanh chóng. Để giúp bạn nắm bắt dễ dàng hơn, chúng tôi sẽ đưa ví dụ minh họa để bạn tham khảo.

Chúng ta có bảng tính

Ví dụ cho trước một bảng dữ liệu

Ví dụ cho trước một bảng dữ liệu

Yêu cầu: Hãy tách riêng “Phường, Quận và Thành phố” ra thành từng cột riêng lẻ.

Cách thực hiện như sau:

Bước 1: Hãy xác định ký tự đầu tiên chúng ta sẽ lấy trong ô địa chỉ bằng công thức =SEARCH(“P.”, A2)

Hàm tách địa chỉ ra thành từng cột riêng lẻ

Hàm tách địa chỉ ra thành từng cột riêng lẻ

Nhấn Enter. Sau đó fill công thức vào tất cả các ô còn lại. Chúng ta có bảng tính như sau

Fill công thức ra các ô còn lại

Fill công thức ra các ô còn lại

Bước 2: Dùng hàm Right để xác định chuỗi giá trị chúng ta sẽ sử dụng theo yêu cầu (bao gồm phường, quận, tỉnh) với công thức =RIGHT(B2,LEN(B2)-(C2-1))

Sử dụng công thức hàm để xác định chuỗi giá trị

Sử dụng công thức hàm để xác định chuỗi giá trị

Nhấn Enter. Sau đó fill công thức vào tất cả các ô còn lại. Chúng ta có bảng tính như sau

Fill công thức vào tất cả các ô

Fill công thức vào tất cả các ô

Bước 3: Tách tên Phường, Quận, Thành phố ra thành 3 cột riêng biệt theo trình tự sau:

Bạn hãy chọn vùng công thức dữ liệu chứa địa chỉ chúng ta sẽ dùng, tức vùng chọn D2:D7

Tách tên Phường, Quận, Thành phố ra thành 3 cột riêng biệt

Tách tên Phường, Quận, Thành phố ra thành 3 cột riêng biệt

Sau đó nhấn chuột phải vào vùng chọn và chọn Copy

Chọn Copy

Chọn Copy

Tiếp theo hãy nhấn chuột phải vào vùng chọn một lần nữa và chọn Paste Special, chọn tiếp Value.. Sau khi bạn nhấn enter, nội dung trong các ô sẽ được chuyển từ dạng công thức thành văn bản thông thường.

Chọn Paste Special

Chọn Paste Special

Tiếp theo chúng ta hãy chọn vùng chọn D2:D7

Bạn hãy vào tab Data trên thanh công cụ bảng tính. Chọn tiếp mục Text To Columns. 

Khi hộp thoại hiển thị, bạn hãy chọn “Delimited”. Sau đó bấm chọn “Next”

Thao tác chọn Delimited

Thao tác chọn Delimited

Khi hộp thoại mới hiển thị bạn hãy chọn tiếp “Comma” sau đó nhấn Finish. Và đây là kết quả

Kết quả cuối cùng nhận được

Kết quả cuối cùng nhận được

Tách địa chỉ trong Excel bằng Find and Replace

Chúng ta có bảng dữ liệu

Bảng dữ liệu cho trước 

Bảng dữ liệu cho trước 

Yêu cầu: Hãy dùng cách tách địa chỉ trong excel bằng phương pháp sử dụng tính năng Find and Replace để tách lấy tên tỉnh/thành phố trong ô “Địa chỉ”

Cách thực hiện như sau:

Bước 1: Hãy tạo bảng copy nội dung ở cột địa chỉ

Tạo bảng copy ở cột địa chỉ

Tạo bảng copy ở cột địa chỉ

Bước 2: Tạo vùng chọn D2:D7

Bước 3: Nhấn tổ hợp phím Ctrl + H để hiển thị tính năng Find and Replace trong bảng tính

Bảng hiển thị tính năng Find and Replace

Bảng hiển thị tính năng Find and Replace

Bước 4: Tại ô Find what, bạn nhập (*, ) để tách lấy tên Thành phố. 

Lưu ý: Sở dĩ chúng ta nhập *, vào ô Find what là vì:

  • Ký hiệu *: đại diện cho một chuỗi ký tự không xác định ở phía trước.

  • Dấu phẩy “,”: là đại diện cho ký tự liền kề phía trước chuỗi ký tự chúng ta cần lấy.

  • “ “ (khoảng trắng”: là đại diện cho phần ký tự chúng ta sẽ lấy ở phía sau.

Nhập (*, ) để tách lấy tên Thành phố

Nhập (*, ) để tách lấy tên Thành phố

Sau đó chọn Replace All và chúng ta có kết quả

Kết quả nhận được

Kết quả nhận được

Nhấn Ok để xác nhận.

>>> Xem thêm: 5 cách đổi đuôi XLXS sang XLS đơn giản, chi tiết nhất

Kết hợp nhiều hàm để tách địa chỉ

Để tách địa chỉ ở đầu và cuối thì sẽ có phần phức tạp hơn, khi này bạn sẽ phải kết hợp nhiều hàm với nhau. Ví dụ cụ thể như sau:

Ví dụ có một bảng dữ liệu

Ví dụ có một bảng dữ liệu

Để tách phường, bạn có thể sử dụng công thức sau:

=MID(B2, FIND(“,”, B2, FIND(“,”, B2) + 1) + 1, FIND(“,”, B2, FIND(“,”, B2) + 1, FIND(“,”, B2) + 1) – (FIND(“,”, B2, FIND(“,”, B2) + 1) + 1))

Công thức này sẽ trả về kết quả là Phường Ngô Thì Nhậm.

Tổng kết

Qua bài viết này bạn có thể biết được cách tách địa chỉ trong excel một cách nhanh chóng và đơn giản. Nếu bạn muốn cải thiện kỹ năng tin học văn phòng thì đừng bỏ lỡ khóa học excel cơ bản đến nâng cao trên Unica nhé.

Tìm hiểu thêm:  Cách tạo nút bấm trong Excel để chạy các lệnh lập trình tự động...