Tách tên TỈNH từ địa chỉ có sẳn (1 người xem)

[tuananhf5]

các cao thu giup em voi....em muon tach tu mot cell gom xa huyen tinh (Thái Bình, Châu Thành, Tây Ninh) em muon tach lam 3 cell xa huyen tinh rieng thi lam sao...cam on

 

[kbw1]

các cao thu giup em voi....em muon tach tu mot cell gom xa huyen tinh (Thái Bình, Châu Thành, Tây Ninh) em muon tach lam 3 cell xa huyen tinh rieng thi lam sao...cam on

Bạn dùng Office 2013
dùng text to colum thử xem sao.

 

[tamthat]

Xin góp 1 hàm để lấy tên tỉnh cho thớt:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,LEN(A2)-10),",",REPT(" ",255)),255))
=LEFT(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),355)),LEN(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),355)))-9)

 

[thangbithu]

các cao thu giup em voi....em muon tach tu mot cell gom xa huyen tinh (Thái Bình, Châu Thành, Tây Ninh) em muon tach lam 3 cell xa huyen tinh rieng thi lam sao...cam on

Ví dụ: chuỗi "Thái Bình, Châu Thành, Tây Ninh" đặt ở A1, công thức:
B1 = TRIM(MID(SUBSTITUTE(", "&$A$1;", ";REPT(" ";200));COLUMN(A1)*200;200))
kéo công thức qua phải

 

[bebo021999]

Thực tế chẳng đơn giản chỉ có chuỗi "Thái Bình, Châu Thành, Tây Ninh" ở trong ô đâu. Bạn đưa khoảng chục dòng dữ liệu thực lên xem

 

[thangbithu]

Thực tế chẳng đơn giản chỉ có chuỗi "Thái Bình, Châu Thành, Tây Ninh" ở trong ô đâu. Bạn đưa khoảng chục dòng dữ liệu thực lên xem

Anh ơi, chủ thớt này có vẻ hỏi cho vui thôi, người ta giúp như thế mà chẳng thấy xuất hiện

 

[VetMini]

các cao thu giup em voi....em muon tach tu mot cell gom xa huyen tinh (Thái Bình, Châu Thành, Tây Ninh) em muon tach lam 3 cell xa huyen tinh rieng thi lam sao...cam on

Đầu tiên hết, em nên đọc kỹ quy luật diễn đàn. Kế đó tập gõ tiếng Việt có dấu.
Bởi vì bài của em phạm quy cho nên "phan lon cac cao thu tranh khong muon giup em"

 

[hoangmaisalem]

Em vẫn chưa hiểu rõ về vấn đề này, mong anh có hình ảnh các thao tác được không ạ?
Vì em đã gán define name nhưng không biết gán hàm ntn tiếp. Cảm ơn anh ạ

 

[ndu96081631]

Em vẫn chưa hiểu rõ về vấn đề này, mong anh có hình ảnh các thao tác được không ạ?
Vì em đã gán define name nhưng không biết gán hàm ntn tiếp. Cảm ơn anh ạ

Xem bài 37 là cách làm mới nhất, khỏi phải gán thêm define name gì cả

 

[Hai Van]

Rất cảm ơn các bạn đã đưa ra giải pháp hay cho việc tách tên tỉnh. Mình cũng đang có bài toán tương tự, nhưng mình có cột tỉnh riêng rồi, cái mình cần là tách tên huyện từ ô địa chỉ. Mình áp dụng một cách máy móc công thức giống như tách tên tỉnh cho việc tách huyện nhưng kết quả còn nhiều chỗ chưa đúng. Mình có vài điểm chưa hiểu mong các bạn giải thích hộ.

1. Trong công thức, mình không hiểu lắm việc dùng "2" cho LOOKUP_VALUE và "1/" cho LOOKUP_VECTOR trong hàm LOOKUP. Có lẽ đây là câu hỏi mình mong được giải đáp nhất.
2. Trong bảng mã quận huyện hiện nay có các quận số (như Quận 1, TPHCM), ở tên quận chỉ có số 1, điều này khiến cho kết quả bị sai khi trong địa chỉ có số nhà. Với riêng bài toán này, mình có thể sửa lại bảng mã huyện để thêm chữ Quận vào trước chữ số (ví dụ như Quận 1 thay vì số 1), tuy nhiên, khi ghép thành tên quận nói chung nó sẽ thành Quận Quận 1. Liệu có cách nào khác việc sửa bảng mã đối với các số này không?
3. Khi tên huyện trùng nhau, hàm có thể trả về đúng tên huyện nhưng lại sai tên tỉnh và sai mã huyện, tỉnh. Làm cách nào để tham chiếu thêm đến tên tỉnh để cho ra đúng mã huyện (các tỉnh miền Tây Nam Bộ hầu hết đều có huyện Châu Thành)

Mình gửi kèm file có kèm bảng mã huyện và công thức mình thử. Mong nhận được hỗ trợ của các bạn. Mình cảm ơn!

 

[ndu96081631]

Rất cảm ơn các bạn đã đưa ra giải pháp hay cho việc tách tên tỉnh. Mình cũng đang có bài toán tương tự, nhưng mình có cột tỉnh riêng rồi, cái mình cần là tách tên huyện từ ô địa chỉ. Mình áp dụng một cách máy móc công thức giống như tách tên tỉnh cho việc tách huyện nhưng kết quả còn nhiều chỗ chưa đúng. Mình có vài điểm chưa hiểu mong các bạn giải thích hộ.

1. Trong công thức, mình không hiểu lắm việc dùng "2" cho LOOKUP_VALUE và "1/" cho LOOKUP_VECTOR trong hàm LOOKUP. Có lẽ đây là câu hỏi mình mong được giải đáp nhất.
2. Trong bảng mã quận huyện hiện nay có các quận số (như Quận 1, TPHCM), ở tên quận chỉ có số 1, điều này khiến cho kết quả bị sai khi trong địa chỉ có số nhà. Với riêng bài toán này, mình có thể sửa lại bảng mã huyện để thêm chữ Quận vào trước chữ số (ví dụ như Quận 1 thay vì số 1), tuy nhiên, khi ghép thành tên quận nói chung nó sẽ thành Quận Quận 1. Liệu có cách nào khác việc sửa bảng mã đối với các số này không?
3. Khi tên huyện trùng nhau, hàm có thể trả về đúng tên huyện nhưng lại sai tên tỉnh và sai mã huyện, tỉnh. Làm cách nào để tham chiếu thêm đến tên tỉnh để cho ra đúng mã huyện (các tỉnh miền Tây Nam Bộ hầu hết đều có huyện Châu Thành)

Mình gửi kèm file có kèm bảng mã huyện và công thức mình thử. Mong nhận được hỗ trợ của các bạn. Mình cảm ơn!

Tìm tên huyện tức là tìm theo 2 điều kiện: Vừa đúng tỉnh vừa đúng huyện (bởi có trường hợp tỉnh này có huyện trùng tên với huyện của tỉnh khác)
Vậy công thức sửa lại là:
1> Tìm tên huyện (dùng COUNTIF)

=LOOKUP(2,1/COUNTIF($A2,"*"&Ten_tinh&"*")/COUNTIF($A2,"*"&Ten_huyen&"*"),Ten_huyen)

2> Tìm tên huyện (dùng SEACH) <--- Dùng SEARCH sẽ không phân biệt chữ HOA chữ thường

=LOOKUP(2,1/SEARCH(Ten_tinh,$A2)/SEARCH(Ten_huyen,$A2),Ten_huyen)

3> Tìm mã huyện (dùng COUNTIF)

=LOOKUP(2,1/COUNTIF($A2,"*"&Ten_tinh&"*")/COUNTIF($A2,"*"&Ten_huyen&"*"),Ma_huyen)

4> Tìm mã huyện (dùng SEARCH)

=LOOKUP(2,1/SEARCH(Ten_tinh,$A2)/SEARCH(Ten_huyen,$A2),Ma_huyen)

-----------------------------------
Có một vài chỗ bị lỗi là do:
- Data nhập sai chính tả, chẳng hạn ghi Đắc lắc thay vì là Đăk Lăk hoặc TP HCM thay vì đúng phải là TP. Hồ Chí Minh
- Data nhập có tên tỉnh nhưng thiếu tên huyện nên không tìm thấy
vân vân...

 

[Hai Van]

Cảm ơn ndu96081631 đã sửa giúp công thức. Bạn có viết là hàm FIND nhanh hơn hàm SEARCH, đặc biệt với dữ liệu lớn. Dữ liệu của mình trên 30K dòng, nên mình dùng hàm FIND và khắc phục nhược điểm chữ hoa chữ thường bằng cách dùng kèm với hàm LOWER. Mình thấy FIND đúng là nhanh hơn hẳn. Một lần nữa cảm ơn bạn đã hỗ trợ.

Bạn có thể giải thích cho mình ý nghĩa của số 2 và số 1 trong công thức không? Ký tự "/" trong công thức mình hiểu là một dạng kiểu như hàm AND có đúng không bạn? Mình muốn hiểu rõ để có thể áp dụng cho các trường hợp khác nữa. Hoặc bạn có thể cho link để mình tìm nếu câu hỏi này đã được trả lời ở đâu đó (vì mình tìm không thấy). Một lần nữa cảm ơn bạn.

 

[ndu96081631]

Bạn có thể giải thích cho mình ý nghĩa của số 2 và số 1 trong công thức không? Ký tự "/" trong công thức mình hiểu là một dạng kiểu như hàm AND có đúng không bạn? Mình muốn hiểu rõ để có thể áp dụng cho các trường hợp khác nữa. Hoặc bạn có thể cho link để mình tìm nếu câu hỏi này đã được trả lời ở đâu đó (vì mình tìm không thấy). Một lần nữa cảm ơn bạn.

Cú pháp mà tôi thường dùng cho bài toán tìm kiếm nhiều điều kiện nó thế này:

=LOOKUP(2, [COLOR=#ff0000]1/(Điều kiện 1)/(Điều kiện 2)/(....)/(Điều kiện n)[/COLOR], [COLOR=#0000cd]Vùng kết quả[/COLOR])

/ là phép chia thôi. Bạn có nghĩa mường tượng nó là AND cũng được.
Nguyên cái đống màu đỏ ở trên nghĩa là số 1 chia cho các điều kiện. Mỗi điều kiện là 1 biểu thức trả về 1 mảng gồm kết quả TRUE, FALSE hoặc number gì đó tùy ý. Ví dụ (A1:A10="OK") sẽ cho kết quả 1 mảng 10 phần tử toàn TRUE hoặc FALSE vậy 1/(A1:A10="OK") sẽ là số 1 chia cho mảng TRUE, FALSE ấy, kết quả nhận được sẽ là số 1 hoặc lỗi DIV/0
Vậy sau khi chia tưa lưa hột dưa như cú pháp trên, kết quả nhận được sẽ là 1 mảng gồm các số 1 (hoặc nhỏ hơn 1) và lỗi DIV/0
Cú pháp lookup tôi ghi ở trên sẽ tìm số 2 trong mảng màu đỏ, tìm thấy bất cứ số nào <2 cuối cùng nó sẽ lấy rồi tra sang vùng kết quả tương ứng. Nếu có nhiều kết quả tìm thấy, LOOKUP (như cú pháp trên) sẽ lấy kết quả sau cùng.
Đương nhiên bạn có thể thay số 2 thành bất cứ số nào mà bạn tin rằng nó lớn hơn bất cứ phần tử nào trong mảng màu đỏ kia
-------------------------
Mệt quá! Nói một hồi có khi mình cũng... khình luôn!
Bạn hiểu thì tốt, nếu chưa hiểu cũng không sao, cứ y chang cú pháp lookup tôi ghi mà phang tới là được (mai này từ từ hiểu hoặc vẫn không hiểu )
Ẹc... Ẹc...

 

[Hai Van]

Oa, quả là vi diệu. Mình hiểu cách bạn đặt điều kiện rồi. Không thể nói gì ngoài từ xuất sắc. Cảm ơn bạn rất nhiều!

 

[Hai Van]

Oa, quả là vi diệu. Mình hiểu cách bạn đặt điều kiện rồi. Không thể nói gì ngoài từ xuất sắc. Cảm ơn bạn rất nhiều!

Cú pháp mà tôi thường dùng cho bài toán tìm kiếm nhiều điều kiện nó thế này:

=LOOKUP(2, [COLOR=#ff0000]1/(Điều kiện 1)/(Điều kiện 2)/(....)/(Điều kiện n)[/COLOR], [COLOR=#0000cd]Vùng kết quả[/COLOR])

/ là phép chia thôi. Bạn có nghĩa mường tượng nó là AND cũng được.
Nguyên cái đống màu đỏ ở trên nghĩa là số 1 chia cho các điều kiện. Mỗi điều kiện là 1 biểu thức trả về 1 mảng gồm kết quả TRUE, FALSE hoặc number gì đó tùy ý. Ví dụ (A1:A10="OK") sẽ cho kết quả 1 mảng 10 phần tử toàn TRUE hoặc FALSE vậy 1/(A1:A10="OK") sẽ là số 1 chia cho mảng TRUE, FALSE ấy, kết quả nhận được sẽ là số 1 hoặc lỗi DIV/0
Vậy sau khi chia tưa lưa hột dưa như cú pháp trên, kết quả nhận được sẽ là 1 mảng gồm các số 1 (hoặc nhỏ hơn 1) và lỗi DIV/0
Cú pháp lookup tôi ghi ở trên sẽ tìm số 2 trong mảng màu đỏ, tìm thấy bất cứ số nào <2 cuối cùng nó sẽ lấy rồi tra sang vùng kết quả tương ứng. Nếu có nhiều kết quả tìm thấy, LOOKUP (như cú pháp trên) sẽ lấy kết quả sau cùng.
Đương nhiên bạn có thể thay số 2 thành bất cứ số nào mà bạn tin rằng nó lớn hơn bất cứ phần tử nào trong mảng màu đỏ kia
-------------------------
Mệt quá! Nói một hồi có khi mình cũng... khình luôn!
Bạn hiểu thì tốt, nếu chưa hiểu cũng không sao, cứ y chang cú pháp lookup tôi ghi mà phang tới là được (mai này từ từ hiểu hoặc vẫn không hiểu )
Ẹc... Ẹc...

 

[Hai Van]

Cú pháp lookup tôi ghi ở trên sẽ tìm số 2 trong mảng màu đỏ, tìm thấy bất cứ số nào <2 cuối cùng nó sẽ lấy rồi tra sang vùng kết quả tương ứng. Nếu có nhiều kết quả tìm thấy, LOOKUP (như cú pháp trên) sẽ lấy kết quả sau cùng.

Bạn cho hỏi thêm về lấy "kết quả sau cùng" mà bạn nói trên. Công thức mình dùng là

=LOOKUP(2,1/FIND(LOWER(Prov_name),LOWER($A2))/FIND(LOWER(Dist_name),LOWER($A2)),Dist_name)

Trong khi công thức cho kết quả đúng ở hầu hết các tỉnh thì riêng các địa chỉ tại TPHCM công thức trả về kết quả sai rất nhiều. Ví dụ

"17 Bà Huyện Thanh Quan, quận 3, TPHCM" ---> CT trả về là Q7 thay vì Q3
"491/15 Trường Chinh, quận Tân Bình, TPHCM" ---> CT lại trả về là Q9 thay vì quận Tân Bình
"198 đường Mã Lò, phường Bình Trị Đông A, quận Bình Tân, TPHCM" ---> CT trả về là Q8 thay vì quận Bình Tân

Một số trường hợp khác thì sai khi tên đường/phố trùng với tên quận, huyện, ví dụ:
"49 Hai Bà Trưng, quận Hoàn Kiếm, Hà Nội" ---> hàm sẽ trả về là quận Hai Bà Trưng mà đúng ra phải là quận Hoàn Kiếm.

Mình không hiểu cái "kết quả cuối cùng" mà hàm trả về trong các trường hợp này. Bạn có thể chỉ giúp cách thêm hàm/điều kiện vào công thức để lọc các trường hợp tìm thấy nhiều kết quả thì hàm trả về kết quả là tên huyện/quận ở vị trí gần cuối trong địa chỉ (vì hầu hết quận, huyện sẽ ở cuối địa chỉ, chỉ trước tên tỉnh). Cảm ơn bạn.

 

[ndu96081631]

Trong khi công thức cho kết quả đúng ở hầu hết các tỉnh thì riêng các địa chỉ tại TPHCM công thức trả về kết quả sai rất nhiều. Ví dụ

"17 Bà Huyện Thanh Quan, quận 3, TPHCM" ---> CT trả về là Q7 thay vì Q3
"491/15 Trường Chinh, quận Tân Bình, TPHCM" ---> CT lại trả về là Q9 thay vì quận Tân Bình
"198 đường Mã Lò, phường Bình Trị Đông A, quận Bình Tân, TPHCM" ---> CT trả về là Q8 thay vì quận Bình Tân

Một số trường hợp khác thì sai khi tên đường/phố trùng với tên quận, huyện, ví dụ:
"49 Hai Bà Trưng, quận Hoàn Kiếm, Hà Nội" ---> hàm sẽ trả về là quận Hai Bà Trưng mà đúng ra phải là quận Hoàn Kiếm.

Mình không hiểu cái "kết quả cuối cùng" mà hàm trả về trong các trường hợp này. Bạn có thể chỉ giúp cách thêm hàm/điều kiện vào công thức để lọc các trường hợp tìm thấy nhiều kết quả thì hàm trả về kết quả là tên huyện/quận ở vị trí gần cuối trong địa chỉ (vì hầu hết quận, huyện sẽ ở cuối địa chỉ, chỉ trước tên tỉnh). Cảm ơn bạn.

1> Những địa chỉ thuộc TP.HCM bị sai là do data của bạn sai. Chuyển sang sheet "HUYEN", filter khu vực "Hồ Chí Minh" bạn sẽ biết lý do (thừa quá trời quận luôn).
2> Khu vực Hà Nội, địa chỉ "49 Hai Bà Trưng, quận Hoàn Kiếm, Hà Nội" bị sai là do Hà Nội vừa có quận Hai Bà Trưng, vừa có quận Hoàn Kiếm. Vậy bạn có thể sửa bằng cách thêm chữ "Quận" vào cột D sheet HUYEN (Quận Ba Đình, Quận Hoàn Kiếm...). Hoặc dời Hai Bà Trưng lên đâu còn Hoàn Kiếm xuống dưới. Như đã nói ở bài trước, LOOKUP sẽ tìm và ưu tiên cái cuối cùng trong trường hợp tìm thấy 2 kết quả
3> Một vài chỗ nhập liệu không thống nhất, ví dụ chỗ thì Đức Hòa, chỗ lại Đức Hoà (sai vị trí dấu huyền) nên bị lỗi
-------------------------
Nói chung nhập liệu lung tung thì không có cách nào tìm chính xác 100% được. Chỗ nào sai đành phải kiểm tra thủ công thôi

 

[Hai Van]

Bạn cho hỏi là có thể lồng hàm MAX vào công thức này không để nếu tìm thấy hai tên tỉnh thì nó sẽ lấy tên xuất hiện sau cùng trong công thức? Mình thử mãi chưa ra. Cảm ơn bạn.

Ví dụ lại hai trường hợp trước:
Khách sạn Hòa Bình, số 9 Võ Thị Sáu, Biên Hòa, Đồng Nai
2 Điện Biên Phủ, Q Ninh Kiều, Cần Thơ

Dùng cách cũ của bạn (tức là có thêm TIM) thì ok vì có hàm MAX, nhưng cách ở bài này thì không có nên cho kết quả sai.

Ngày xưa còn... NGU nên công thức cũng lòng vòng
Nếu là bây giờ thì tôi sẽ làm khác:
- Chỉ cần 1 name DMT (bỏ name TIM)
- Công thức tại C2:

=LOOKUP(2,1/COUNTIF($A2,"*"&DMT&"*"),DMT)

Hoặc:

=LOOKUP(2,1/FIND(DMT,$A2),DMT)

 

[mythuat04p]

Công thức cũ của anhtuan hay hơn công thức mới. công thức mới hạn chế nhiều lắm

 

[mythuat4p]

Ngày xưa còn... NGU nên công thức cũng lòng vòng
Nếu là bây giờ thì tôi sẽ làm khác:
- Chỉ cần 1 name DMT (bỏ name TIM)
- Công thức tại C2:

=LOOKUP(2,1/COUNTIF($A2,"*"&DMT&"*"),DMT)

Hoặc:

=LOOKUP(2,1/FIND(DMT,$A2),DMT)

Công thức này ngắn nhưng không hay bằng công thức đầu. Công thức này bị hạn chế DMT. Không được có dòng trống