Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Nội dung được viết bởi Tuấn Ba

Chúng ta có một danh sách bao gồm địa chỉ và phân cách với nhau bằng một ký tự hay ký hiệu nào đó, vậy làm cách nào chúng ta có thể tách chúng ra thành từng cột, với ký tự đó, trong bài viết này Gitiho sẽ cùng các bạn thực hiện nhé.

Chúng ta có địa chỉ như sau:

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Trong hình ảnh trên là địa chỉ của Gitiho vậy là sao chúng ta có thể tách riêng biệt địa chỉ ra thành từng thành phần khác nhau.

Thực hiện tách phần tỉnh ra khỏi địa chỉ

Để tách được phần tỉnh ra ta cần sử dụng hàm sau:

Cách bạn sử dụng công thức tổng quát sau:

=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(ô cần tách,"ký tự phân cách",REPT("ký tự phân cách",LEN(ô cần tách))), LEN(ô cần tách)),"ký tự phân cách",""))

Trong công thức trên chúng ta sử dụng như sau:

=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,"-",REPT("-",LEN(A2))), LEN(A2)),"-",""))

Hàm REPT là hàm để lặp lại ký tự hay một câu, một đoạn lên số lần nào đó vậy dùng hàm REPT như thế nào các bạn đọc bài viết sau:

 =REPT("-",LEN(A2))

Hàm REPT sẽ lặp lại số lượng dấu phân cách lên bằng chính độ dài của đoạn ký tự chúng ta đưa vào.

Sử dụng hàm SUBSTITUTE như thế nào trong excel cách bạn tham khảo bài viết sau:

Trong công thức bên dưới ta đã lấy được một chuỗi có phân tách ký tự lặp lại bằng  chiều dài chuỗi

=SUBSTITUTE(A2,"-",REPT("-",LEN(A2)))

Vậy dùng hàm nào để lấy được ký tự bên phải(phần cuối cùng) chúng ta chỉ cần thêm hàm RIGHT vào nữa là xong, Bài viết về hàm RIGHT các bạn xem tại đây:

=RIGHT(SUBSTITUTE(A2,"-",REPT("-",LEN(A2))), LEN(A2))

Với công thức trên chúng ta đã lấy ra được phần Tỉnh của chuỗi nhưng lại có phần ký tự "-" và đôi khi có ký dấu cách vậy chúng ta sẽ kết hợp thêm hàm TRIM và SUBSTITUTE để lại bỏ nhé:

TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,"-",REPT("-",LEN(A2))), LEN(A2)),"-",""))

Thực hiện tách địa chỉ Phường và tên đường

Trong ví dụ trên chúng ta để ý thấy phân cách các thành phần ở đây sử dụng dấu "-" để phân cách các thành phần ta sẽ lợi dụng điểm này để tách các ký tự ra.

Vậy hàm nào dùng để tìm ký tự đó?

Chúng ta có thể sử dụng 1 trong hai hàm sau: 

Hàm SEARCH hoặc Hàm FIND

Vậy cú pháp của hai hàm đó thế nào các bạn có thể thao khảo bài viết sau:

Trong ví dụ trên ta làm sao để tìm được ký tự?

Chúng ta sử dụng công thức sau: = SEARCH("-",A2) hoặc FIND("-",A2)

Nhưng ở đây lại ra một kết quả là con số sao lại tách được ra địa chỉ nhỉ? dùng hàm nào để tách ký tự ở phần bên trái nhỉ.

Chúng ta sẽ sử dụng hàm LEFT để lấy ký tự bên trái ra nhé.

Các sử dụng hàm LEFT trong excel bạn tham khảo bài viết sau:

Giờ chúng ta chỉ cần ghép công thức vào nữa là xong vấn đề tách Đường và phường ra như sau:

=LEFT(A2,SEARCH("-",A2)-1) hoặc =LEFT(A2,FIND("-",A2)-1) 

Tại sao lại phải trừ đi 1 đơn vị vì hàm lấy SEARCH("-",A2) hoặc FIND("-",A2) tìm kiếm đến ký tự phân cách nên ta cần trừ đi một ký tự để lấy chính xác ra phần ký tự chúng ta cần.

Vậy công thức chung để tách lấy ra phần ký tự là tên đường, phương sẽ như sau:

=LEFT(ô cần tách,SEARCH("ký tự phân cách",ô cần tách)-1)


 =LEFT(ô cần tách,FIND("ký tự phân cách",ô cần tách)-1) 

Hoặc các bạn có thể sử dụng hàm sau:

=TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(ô cần tách,"ký tự phân cách",REPT("ký tự phân cách",LEN(ô cần tách))), LEN(ô cần tách)),"ký tự phân cách",""))

Thực hiện tách tên quận hoặc huyện ra khỏi chuỗi

Ở đây có hai cách tách:

Với cách 1 chúng ta có thể sử dụng hàm SUBSTITUTE để thay thế phần Tỉnh Tỉnh và  và đường được tách ra như sau:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,""),"-",""))

Chúng ta lồng các hàm SUBSTITUTE lại với nhau.

Cách tách thứ hai không phục thuộc vào những ký tự đã tách

Công thức tổng quát như sau:

TRIM(SUBSTITUTE(MID(SUBSTITUTE(ô cần tách,"ký tự phân cách",REPT("ký tự phân cách",LEN(ô cần tách))), LEN(ô cần tách), LEN(ô cần tách)),"ký tự phân cách",""))

Trong công thức tông quát chúng ta thấy nó gần giống với với phần sử dụng hàm RIGHT chỉ khác sử dụng hàm MID thôi đúng không? vậy cách sử dụng hàm MID thế nào trong Excel bạn tham khảo bài viết sau đây:

Công thức tổng quát:

=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,"-",REPT("-",LEN(A2))), LEN(A2)),"-",""))

Thật đơn giản phải không nào chỉ với những bước đơn giản chúng ta đã tách được chuỗi theo điều kiện, phân cách theo ký tự nào đó.

Các bạn xem thêm video để hiểu rõ hơn nhé.



Tài liệu kèm theo bài viết

KHÓA HỌC EXCEL ONLINE HOÀN TOÀN MIỄN PHÍ

Khóa học với hơn 14000 HỌC VIÊN đang học và đánh giá trung bình 4.76 SAO

5/5 - (2 bình chọn)

5/5 - (2 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông