Tin NóngTrong 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
- 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é.
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
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
Y=SEARCH(“, Q”,B2)
Á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
>>> 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 “phường” trong địa chỉ
Nhấn Enter để nhận kết quả
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
Nhấn Enter để nhận kết quả
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
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
Và đây là 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
Bước 4: Nhấn Enter để nhận kết quả
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
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ẻ
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
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ị
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 ô
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
Sau đó nhấn chuột phải vào vùng chọn và 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
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
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
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
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ỉ
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ướ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ố
Sau đó chọn Replace All và chúng ta có kết quả
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
Để 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é.