Bạn có 1 danh sách gồm đầy đủ các thông tin như số nhà, tên đường, phường, quận, thành phố. Bạn muốn tách riêng địa chỉ Phường, Quận, Thành phố ra từng cột nhưng không biết cách làm? Hãy cùng Học Excel Online tìm hiểu cách tách quận huyện thành phố ra riêng từng cột trong địa chỉ trong bài viết này nhé.

Ví dụ như sau:

Yêu cầu của chúng ta là phải tách được tên Phường, Quận, Thành phố ra các cột C, D, E từ địa chỉ trong cột A. Đặc điểm dữ liệu ở đây là:

• Lúc thì viết tắt (P.7, Q Go Vap, Tp.HCM) lúc lại viết đầy đủ (Phuong, Quan, TP. Ho Chi Minh)
• Độ dài ký tự không giống nhau.

Để làm được yêu cầu này, chúng ta cần tìm hiểu một số kiến thức sau:

Cách sử dụng hàm SEARCH

Hàm Search là hàm giúp tìm một chuỗi ký tự nhỏ trong 1 chuỗi ký tự lớn, để xác định vị trí của chuối ký tự nhỏ.

Mục đích của hàm Search ở đây là gì? Khi nhìn trong đoạn text địa chỉ, chúng ta thấy có 1 dấu hiệu nhận biết vị trí bắt đầu của đoạn text cần tách:

Đó chính là luôn bắt đầu với ký tự P, trước đó là ký tự dấu phẩy và dấu cách.

Như vậy tại vị trí này, chúng ta sẽ có thể tách địa chỉ thành 2 phần:

• Phần số nhà, tên đường
• Phần tên Phường, Quận, Thành phố

Hàm Search được viết như sau:

B2=SEARCH(“, P”, A2)

• Tìm theo chuỗi ký tự là “, P” gồm dấu phẩy + dấu cách + chữ P
• Tìm theo dãy ký tự trong ô A2

Kết quả của hàm Search như sau:

Cách sử dụng hàm RIGHT để tách Phường, Quận, Thành Phố

Tiếp theo là lấy phần ký tự bên phải của Địa chỉ, bắt đầu từ vị trí đã xác định bởi hàm SEARCH.

Nguyên tắc để lấy:

• Sử dụng hàm Right
• Ký tự cần tách: ở cột Địa chỉ
• Độ dài ký tự cần lấy: Tính từ vị trí xác định bởi hàm Search tới hết phần bên phải. Tương ứng với việc Bỏ toàn bộ phần ký tự bên trái từ 1 tới vị trí xác định bởi hàm Search

Công thức như sau:

C2=RIGHT(A2,LEN(A2)-B2)

Trong đó:

Độ dài ký tự cần lấy = LEN(A2)-B2

• LEN(A2) là tổng số ký tự ở ô A2
• B2 là phần ký tự bên trái tới vị trí bắt đầu tên Phường đã xác định bởi hàm Search ở trên

Xem thêm: Hàm LEN, hàm đếm số ký tự trong chuỗi

Kết quả ta có:

Sử dụng Text to Column để tách riêng Phường, Quận, Thành phố ra từng cột

Text to Column là một công cụ rất hữu ích trong Excel để tách một đoạn text ra nhiều cột dựa vào 1 ký hiệu hay độ dài của ký tự. Để sử dụng công cụ này cần đảm bảo nội dung được tách phải là Text, không phải là Formula (công thức).

Tại cột C, chúng ta đang có kết quả của công thức xác định bởi hàm RIGHT. Để có thể xóa công thức đi mà vẫn giữ nguyên kết quả của công thức, chúng ta làm như sau:

• Copy vùng ô chứa công thức C2:C5
• Dán đặc biệt phần giá trị (Paste Special > Value) vào ô C2

Xem thêm tại bài viết: Cách thay thế công thức bằng kết quả hoặc giá trị trong Excel

Tiếp theo sử dụng Text to Columns như sau:

• Chọn vùng C2:C5
• Trong thẻ Data chọn Text to Columns

Trong cửa sổ Text to Columns, thực hiện như sau:

• Step 1 of 3: Chọn Delimited rồi bấm Next>
• Step 2 of 3: đánh dấu chọn vào mục Comma (dấu phẩy) rồi bấm Finish

Kết quả là 3 cột Phường, Quận, Thành phố sẽ nhận được các kết quả tương ứng:

Như vậy chỉ với những hàm cơ bản trong Excel, cộng với việc sử dụng công cụ Text to Columns sẵn có của Excel, chúng ta đã có thể thực hiện được yêu cầu tưởng như khá khó khăn này.

Ngoài ra các bạn có thể tham khảo thêm một số bài viết sau:

Hướng dẫn kĩ thuật tách một ô thành nhiều ô dựa vào ngắt dòng trong Excel đơn giản

Cách tách đoạn text từ chuỗi văn bản bằng hàm SUBSTITUTE

Tách tên quận, huyện từ địa chỉ cho trước

Tách số điện thoại, tách email, định dạng ngày tháng bằng Flashfill