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

[Mr Okebab]

Của bác có một số công thức hiển thị kết quả không đúng. Bác xem nhé :

Còn đây là File em sử dụng UFD. Bác xem nhé, nhẹ hơn nhiều.

Function Tinh(DMTinh As Range, Chuoi As String, Kytu As String) As String
    Application.Volatile (False)
    Dim i As Integer, i1 As Integer, i2 As Integer, m As Integer, Temp As String
    For i = Len(Chuoi) To 1 Step -1
        If Mid$(Chuoi, i, 1) = Kytu Then
            m = m + 1
            If m = 1 Then
                i2 = i
            ElseIf m = 2 Then
                i1 = i + 1
                Temp = Trim$(Mid$(Chuoi, i1 + 1, i2 - i1 - 1))
                Exit For
            End If
        End If
    Next
    If WorksheetFunction.CountIf(DMTinh, Temp) > 0 Then Tinh = Temp
End Function

Thân!

 

[SA_DQ]

Thách Tuấn trong vòng 5 gy đó

Đây!, máy của mình báo đây; thay mã tỉnh luôn

Option Explicit: Option Base 1
Const DDai = 15
Sub MaTinh()
 Dim DRow As Long, iJ As Long, Iz As Long, TRow As Long
 Dim StrC As String:     Dim THGian As Double
 
 THGian = Timer
 Application.ScreenUpdating = False
 Sheets("Tinh").Select
 TRow = Cells(65432, 1).End(xlUp).Row
 ReDim MMa(TRow, 2) As String
 For iJ = 2 To TRow
    MMa(iJ, 1) = Cells(iJ, 1):    MMa(iJ, 2) = Cells(iJ, 2)
 Next iJ
  Sheets("DATA").Select
 DRow = Cells(65432, 1).End(xlUp).Row
 For iJ = 2 To DRow
    For Iz = 2 To TRow
        StrC = Cells(iJ, 1)
        If InStr(Len(StrC) - DDai, StrC, MMa(Iz, 2)) > 0 Then
            Cells(iJ, 2) = MMa(Iz, 1):  Exit For
        End If
 Next Iz, iJ
 MsgBox Str(Timer - THGian), , Str(4.125)
End Sub

Còn khá nhiều tỉnh thành sai chính tả, như
Bắc Kạn - Bắc cạn;
TP HCM - Th phố Hồ Chí Minh
Con tum - ConTum
. . . .

 

[anhtuan1066]

Cám ơn Bắp đã phát hiện... Điều này cũng dễ giãi quyết thôi mà...
Sửa name:

=IF(ISERROR(FIND(DMT,DATA!$A2,1)),"",FIND(DMT,DATA!$A2,1))

Thành

=IF(ISERROR(FIND(DMT,RIGHT(DATA!$A2,25),1)),"",FIND(DMT,RIGHT(DATA!$A2,25),1))

Vì đễ ý rằng số ký tự của TINH có độ dài ký tự lớn nhất là 14... cộng với chứ Việt Nam cuối chuổi, các dấu "," và khoảng trắng thì tổng số ký tự cũng ko vượt quá 25 ký tự... he... he...
Nói chung nếu Bắp muốn làm UDF thì cũng nên dựa vào thuật toán này (dò tìm)... ko nên dùng các ký hiệu dấu "," hoặc dấu "-" làm chuẩn... sẽ cho kết quả còn sai trầm trọng hơn nữa...
Qua thử nghiệm với hàm tự tạo của Bắp, nó ko nhận ra dc chuổi khi ta viết ..... - Hà Nội - Việt Nam ... hoặc viết theo kiểu ko có chấm phẩy
Nói chung cũng khó có thể nào tạo ra 1 thuật toán chính xác 100%, tuy nhiên càng tiệm cận với chân lý thì càng tốt...
ANH TUẤN

 

[Mr Okebab]

Cám ơn Bắp đã phát hiện... Điều này cũng dễ giãi quyết thôi mà...
Sửa name:

=IF(ISERROR(FIND(DMT,DATA!$A2,1)),"",FIND(DMT,DATA!$A2,1))

Thành

=IF(ISERROR(FIND(DMT,RIGHT(DATA!$A2,25),1)),"",FIND(DMT,RIGHT(DATA!$A2,25),1))

Vì đễ ý rằng số ký tự của TINH có độ dài ký tự lớn nhất là 14... cộng với chứ Việt Nam cuối chuổi, các dấu "," và khoảng trắng thì tổng số ký tự cũng ko vượt quá 25 ký tự... he... he...
Nói chung nếu Bắp muốn làm UDF thì cũng nên dựa vào thuật toán này (dò tìm)... ko nên dùng các ký hiệu dấu "," hoặc dấu "-" làm chuẩn... sẽ cho kết quả còn sai trầm trọng hơn nữa...
Qua thử nghiệm với hàm tự tạo của Bắp, nó ko nhận ra dc chuổi khi ta viết ..... - Hà Nội - Việt Nam ... hoặc viết theo kiểu ko có chấm phẩy
Nói chung cũng khó có thể nào tạo ra 1 thuật toán chính xác 100%, tuy nhiên càng tiệm cận với chân lý thì càng tốt...
ANH TUẤN

Hàm UF không phải sử dụng cho các TH bác ạ (rất khó cho vấn đề chuỗi)
Cái hay của giải thuật là tìm ra các đặc trưng, đặc thù của TH riêng, sau đó mới làm UF.

Em không có tham vọng làm tất cả các TH "lung tung " của bác nói, chỉ có làm cho TH của File bác thôi. Vì mình phải hướng người khác tiến gần đến cái chuẩn chung mới là giải pháp hữu hiệu. VD như chính tả, vấn đề DS tỉnh, vấn đề chia tách tỉnh)

Thuật toán của em thì chẳng có gì cả, chẳng qua là em cắt chuỗi bắt đầu từ ký tự đặc biệt (ký tự nào cũng được) kế cuối đến ký tự đặc biệt cuối cùng. Sau đó so sánh chuỗi nhận được với danh sách tỉnh, nếu tồn tại thì lấy, nếu không thì . . thôi.

Vì đễ ý rằng số ký tự của TINH có độ dài ký tự lớn nhất là 14... cộng với chứ Việt Nam cuối chuổi, các dấu "," và khoảng trắng thì tổng số ký tự cũng ko vượt quá 25 ký tự... he... he...

VD : Ký tự cuối là : "Thành phố Hồ Chí Minh, Việt Nam" thì nó có đến 31 ký tự
Lúc khác thì : "Thành phố Hồ Chí Minh, Nước Cộng hòa xã hội chủ nghĩa Việt Nam"
Lúc đó thì cả em và bác cùng . . . toi!


Cái này giống như câu hỏi : Làm cách nào tách số nhà, quận , huyện, tỉnh Tp với các chuỗi số không đồng nhất. Vấn đề này đã bàn rất nhiều trên GPE rồi nhưng cũng chưa có giải pháp nào khả thi, vì chẳng có quy luật gì cả.

Nếu bác có hứng thú làm mấy cái công thức dạng của bác để giải quyết vấn đề đó thì mời bác hãy . . thử sức. Như thế thì em mới thấy rằng CT của bác thực sự . . hay. Còn mấy cái TH cụ thể này (như của bác ) thì . . làm thế nào là tùy!!

Thân!

 

[anhtuan1066]

Bắp cho hỏi nha! Nếu ko dựa vào dấu "," tại sao nó lại cho kết quả sai khi tôi bỏ dấu "," đi?

Em không có tham vọng làm tất cả các TH "lung tung " của bác nói, chỉ có làm cho TH của File bác thôi

Thì tôi cũng vậy thôi mà... dựa vào dử liệu cụ thể đễ ra thuật toán.. và trong file này ko có chổ nào là Thành phố Hồ Chí Minh, Nước Cộng hòa xã hội chủ nghĩa Việt Nam cả... he.. he..

Cái này giống như câu hỏi : Làm cách nào tách số nhà, quận , huyện, tỉnh Tp với các chuỗi số không đồng nhất

Ko giống nhau à nha! Của Bắp là tách chuổi, còn của tôi là dò tìm... 2 thuật toán hoàn toàn khác nhau! (Dò tìm dựa vào bảng danh mục có sẳn)

Nếu bác có hứng thú làm mấy cái công thức dạng của bác để giải quyết vấn đề đó thì mời bác hãy . . thử sức. Như thế thì em mới thấy rằng CT của bác thực sự . . hay. Còn mấy cái TH cụ thể này (như của bác ) thì . . làm thế nào là tùy!!

Nói chung tôi cũng ko nghĩ cái tôi làm là hay dở gì cả... Đơn giản vì tôi đang giãi quyết file giúp bạn, đến khúc này thì bí vì ko biết làm sao tách ra dc tên tỉnh nên làm đại 1 CT coi như là hổ trợ cho riêng mình... Tuy nhiên vì chưa hài lòng lắm với CT nên mới đưa lên cho mọi người tham khảo và góp ý... Đâu có vấn đề gì ghê gớm mà Bắp lại thách đố thế chứ.. Trình độ của tôi còn I TỜ IT lắm thì sao dám so sánh với các cao thủ...
Tất cả chỉ là trao đổi kinh nghiệm và học hỏi, mong Bắp hiểu cho điều này!
ANH TUẤN

 

[Mr Okebab]

Bác ạ !

Bắp cho hỏi nha! Nếu ko dựa vào dấu "," tại sao nó lại cho kết quả sai khi tôi bỏ dấu "," đi?

Dĩ nhiên là em dựa vào ký tự , đó chứ, Nếu như chuỗi không sử dụng ký tự đó thì ta chỉ thay trong công thức thôi . Em đã trình bày ở trên rồi mà.

Thì tôi cũng vậy thôi mà... dựa vào dử liệu cụ thể đễ ra thuật toán.. và trong file này ko có chổ nào là Thành phố Hồ Chí Minh, Nước Cộng hòa xã hội chủ nghĩa Việt Nam cả... he.. he..

Em cũng VD để nói rằng nếu không có quy luật thì . . thua. Với TH của bác thì có rất nhiều quy luật, với mỗi quy luật sẽ có các hướng khác nhau

Ko giống nhau à nha! Của Bắp là tách chuổi, còn của tôi là dò tìm... 2 thuật toán hoàn toàn khác nhau! (Dò tìm dựa vào bảng danh mục có sẳn)

Cũng thế thôi bác ạ, Tách ra tên đường, quận, TP. Cái còn lại là số nhà. Mà tên đường, Quận, TP thì sẽ có danh mục (muốn có thì ta lập thôi mà)
DM chẳng qua là để ta tránh sai sót thôi mà.

Nói chung tôi cũng ko nghĩ cái tôi làm là hay dở gì cả... Đơn giản vì tôi đang giãi quyết file giúp bạn, đến khúc này thì bí vì ko biết làm sao tách ra dc tên tỉnh nên làm đại 1 CT coi như là hổ trợ cho riêng mình... Tuy nhiên vì chưa hài lòng lắm với CT nên mới đưa lên cho mọi người tham khảo và góp ý... Đâu có vấn đề gì ghê gớm mà Bắp lại thách đố thế chứ.. Trình độ của tôi còn I TỜ IT lắm thì sao dám so sánh với các cao thủ...

Không nghiêm trọng đâu, ý em muốn nói với bác rằng nếu không hướng người khác đến cách tổ chức làm việc khoa học thì em và bác còn . . khổ nhiều

Tất cả chỉ là trao đổi kinh nghiệm và học hỏi, mong Bắp hiểu cho điều này!
Vâng, em hiểu mà.
Nếu có gì làm bác không vui thì em xin rút lại những lời nói trên.
ANH TUẤN

Thân!

 

[tigertiger]

Cám ơn Bắp đã phát hiện... Điều này cũng dễ giãi quyết thôi mà...
Sửa name:

=IF(ISERROR(FIND(DMT,DATA!$A2,1)),"",FIND(DMT,DATA!$A2,1))

Thành

=IF(ISERROR(FIND(DMT,RIGHT(DATA!$A2,25),1)),"",FIND(DMT,RIGHT(DATA!$A2,25),1))

Vì đễ ý rằng số ký tự của TINH có độ dài ký tự lớn nhất là 14... cộng với chứ Việt Nam cuối chuổi, các dấu "," và khoảng trắng thì tổng số ký tự cũng ko vượt quá 25 ký tự... he... he...
Nói chung nếu Bắp muốn làm UDF thì cũng nên dựa vào thuật toán này (dò tìm)... ko nên dùng các ký hiệu dấu "," hoặc dấu "-" làm chuẩn... sẽ cho kết quả còn sai trầm trọng hơn nữa...
Qua thử nghiệm với hàm tự tạo của Bắp, nó ko nhận ra dc chuổi khi ta viết ..... - Hà Nội - Việt Nam ... hoặc viết theo kiểu ko có chấm phẩy
Nói chung cũng khó có thể nào tạo ra 1 thuật toán chính xác 100%, tuy nhiên càng tiệm cận với chân lý thì càng tốt...
ANH TUẤN

oh, lâu quay lại chủ đề nay, thấy có vẻ càng đi sai hướng rồi, atuan..ơi,

atuan... không nên hạn chế 25 thế ??? theo tigertiger thì sao không dùng thêm countif... để lựa chọn thành phần cuối cùng trong chuỗi ký tự (hợp với DMT) -> khi đó chọn cái cuối cùng _> trong chuỗi đó,..

ý tưởng thế có đc k nhỉ? xin mới cao thủ cho ý kiến ...

 

[anhtuan1066]

atuan... không nên hạn chế 25 thế ??? theo tigertiger thì sao không dùng thêm countif... để lựa chọn thành phần cuối cùng trong chuỗi ký tự (hợp với DMT) -> khi đó chọn cái cuối cùng _> trong chuỗi đó,..

Vâng! Bài của tôi chỉ nêu thuật toán thôi... Còn áp dụng thế nào phải tùy vào trường hợp cụ thể nhất... Như Bắp đã nói rằng thuật toán dựa trên quy luật mà tạo.. đúng ko?
Với trường hợp riêng của bạn thì bạn có thể dùng COUNIF hay cái gì đó.. Nói chung đó ko phải là vấn đề ta quan tâm

 

[Hoàng Trọng Nghĩa]

oh, lâu quay lại chủ đề nay, thấy có vẻ càng đi sai hướng rồi, atuan..ơi,

atuan... không nên hạn chế 25 thế ??? theo tigertiger thì sao không dùng thêm countif... để lựa chọn thành phần cuối cùng trong chuỗi ký tự (hợp với DMT) -> khi đó chọn cái cuối cùng _> trong chuỗi đó,..

ý tưởng thế có đc k nhỉ? xin mới cao thủ cho ý kiến ...

Em có ý kiến nhé các BÁC, ANH TUẤN viết rất hay (em đang nói bài này về công thức, không dám bàn VBA), qua đọc từ đầu đến đuôi và thử nhiều lần, em xin "mượn gió bẻ măng" mạn phép lấy công thức của ANH TUẤN sửa cho ANH TUẤN như sau:

[COLOR=darkorchid]TIM[/COLOR] =IF(ISERROR([COLOR=red]SEARCH[/COLOR](DMT,RIGHT(DATA!$A2,[COLOR=blue]30[/COLOR]),1)),"",[COLOR=red]SEARCH[/COLOR](DMT,RIGHT(DATA!$A2,[COLOR=blue]30[/COLOR]),1))

LÝ DO:

1) Em chọn SEARCH thay cho FIND là vì SEARCH không phân biệt chữ Hoa hay Thường, ví dụ "TP HCM" em chỉ cần gõ "tp hcm" là "KHÔNG TÌM THẤY" ngay. Vấn đề nhanh hay chậm không mấy quan trọng so với kết quả đạt được.

2) Đặt số 30 (có thể hơn) thay cho 25 là vì em thử viết "***, nước Việt Nam" (đảm bảo >=25) thì OK, nhưng em thử đặt "***, Việt Nam country" thì kết quả "KHÔNG TÌM THẤY", nên em chọn là 30.

Kính mong các BÁC, các ANH, CHỊ chỉ giáo thêm.

 

[ndu96081631]

1) Em chọn SEARCH thay cho FIND là vì SEARCH không phân biệt chữ Hoa hay Thường, ví dụ "TP HCM" em chỉ cần gõ "tp hcm" là "KHÔNG TÌM THẤY" ngay. Vấn đề nhanh hay chậm không mấy quan trọng so với kết quả đạt được.
.

Tại bạn chưa biết đó thôi, SEARCH có 1 cái dở hơn FIND, đó là tốc độ ---> Hãy thử với dử liệu thật lớn sẽ thấy SEARCH chậm hơn FIND rất nhiều

 

[Hoàng Trọng Nghĩa]

Tại bạn chưa biết đó thôi, SEARCH có 1 cái dở hơn FIND, đó là tốc độ ---> Hãy thử với dử liệu thật lớn sẽ thấy SEARCH chậm hơn FIND rất nhiều

Dạ, thưa Thầy NDU và Thầy ANH TUẤN, nếu kết quả cho đúng (99%), dĩ nhiên chỉ cần Filter cho những cái "KHÔNG TÌM THẤY" và sửa lại (1%) thì vẫn nhanh hơn là thời gian tính FIND nhiều phải không ạ? Bởi vì đâu mấy ai nhập đúng hoàn toàn trừ khi có Form với Combobox... hỗ trợ phải không ạ?
Còn việc Ctrl+H thì em đồng ý về điều này!
Xin cảm ơn.

 

[msvantinh]

Vì thế nên mới nhờ các cao thủ hoàn thiện... Nhưng may quá, trong danh sách ko có vụ trùng này... Hên thật!

Ứng dụng thì thiếu gì, nếu bạn có làm văn phòng thì thử nghĩ xem! Như tôi hiện giờ cần lấy mã Bưu chính của từng em 1, tất nhiên điều đầu tiên tôi cần phải biết nó thuộc tỉnh gì rồi.. Tách ra xong thì VLOOKUP với bãng mã bưu chính...

Cái này ko cần thiết phải lập công thức, sửa lại bằng tay trong DMT cho tương thích 2 danh sách là dc rồi...
Còn như trong sheet DATA họ viết lẩn lộn giữa 2 cách thì sẽ dùng chức năng Find and Replace của Excel đễ thay thế cho thống nhất 1 kiểu
Mến
ANH TUẤN

Anh Tuấn ơi ,
Em đã áp dụng theo cách anh cho , nhưng không được . Tuy nhiên em thấy nó hay lắm , lại đang cần gấp lắm , em đang tách các quận , huyện ở ĐN , dữ liệu hơn 80.000 , nhưng được cái , ở ĐN chỉ có 7 quận huyện thôi . Nên thấy cách của anh là hay nhất , nhưng em mãi không áp dụng được . Anh giúp em gấp với
Cảm ơn anh

 

[ndu96081631]

Anh Tuấn ơi ,
Em đã áp dụng theo cách anh cho , nhưng không được . Tuy nhiên em thấy nó hay lắm , lại đang cần gấp lắm , em đang tách các quận , huyện ở ĐN , dữ liệu hơn 80.000 , nhưng được cái , ở ĐN chỉ có 7 quận huyện thôi . Nên thấy cách của anh là hay nhất , nhưng em mãi không áp dụng được . Anh giúp em gấp với
Cảm ơn anh

Nếu không áp dụng được thì bạn đưa file lên vậy (chứ biết làm sao)

 

[TuylipJuly]

Bác xem file hộ em với, em làm mãi ho được. Hix!

 

[ndu96081631]

Bác xem file hộ em với, em làm mãi ho được. Hix!

Gõ công thức này vào cell F3:

=LOOKUP(2,1/FIND(DMT,E3),DMT)

Kéo fill xuống... Chổ nào bị lỗi thì tự bạn sửa bằng tay nhé

 

[Gia_Khue]

File này của bác Tuấn hay thật. Mình áp dụng cho đủ thứ. Tách tên tỉnh, tên NH, tên mặt hàng, nói chung là tất cả những gì mà bị gom chung lại 1 cell mà muốn tách ra thì áp dụng được tất.

 

[anhtuan1066]

File này của bác Tuấn hay thật. Mình áp dụng cho đủ thứ. Tách tên tỉnh, tên NH, tên mặt hàng, nói chung là tất cả những gì mà bị gom chung lại 1 cell mà muốn tách ra thì áp dụng được tất.

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)

 

[huuthang_bd]

File này của bác Tuấn hay thật. Mình áp dụng cho đủ thứ. Tách tên tỉnh, tên NH, tên mặt hàng, nói chung là tất cả những gì mà bị gom chung lại 1 cell mà muốn tách ra thì áp dụng được tất.

Cùng còn tùy trường hợp. Hơn nữa phải cẩn thận vì không phải là không có trường hợp kết quả sai. Ví dụ như:

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ơ

 

[Gia_Khue]

Hôm nay áp dụng cho việc tách tên các ngân hàng thì bị gặp 1 vấn đề là hàm Find nó phân biệt cả chữ hoa lẫn chữ thường hay sao í bác Tuấn ạ. Thế là từ raw data, mình lại phải hì hục thêm 1 bước đổi cho thành Upper hoặc Lower hết, còn cái danh mục thì cũng phải đổi thành Upper hoặc Lower hết luôn.

Bác huuthang_bd xem có phải là do vụ chữ hoa, chữ thường mà nó không chạy ra không.

Post xong bài thì thử 2 ví dụ của bác Thắng, chạy vẫn ok mà bác.

 

[ndu96081631]

Hôm nay áp dụng cho việc tách tên các ngân hàng thì bị gặp 1 vấn đề là hàm Find nó phân biệt cả chữ hoa lẫn chữ thường hay sao í bác Tuấn ạ. Thế là từ raw data, mình lại phải hì hục thêm 1 bước đổi cho thành Upper hoặc Lower hết, còn cái danh mục thì cũng phải đổi thành Upper hoặc Lower hết luôn.

Bác huuthang_bd xem có phải là do vụ chữ hoa, chữ thường mà nó không chạy ra không.
.

Thì bạn thay FIND thành SEARCH đi
2 hàm này dùng y chang nhau. Cái khác duy nhất giữa chúng là hàm SEARCH không phân biệt HOA thường (chính thế mà hàm SEARCH cho tốc độ chậm hơn)

Post xong bài thì thử 2 ví dụ của bác Thắng, chạy vẫn ok mà bác.

Cũng chưa chắc đâu. Ví dụ chuổi: Khách sạn Hòa Bình, số 9 Võ Thị Sáu, Biên Hòa, Đồng Nai thì công thức sẽ cho kết quả là Hòa Bình, trong khi phải là Đồng Nai mới đúng ---> Nói chung sẽ tùy trường hợp cụ thể ta sẽ chỉnh sửa cho phù hợp thôi