Hỏi về công thức để tách địa chỉ ra riêng từng ô! (1 người xem)

[banhbao]

thân gửi các bạn trên diễn đàn GPX mình làm về bảo hiểm cho một doanh nghiệp nhỏ và cần điền đầy đủ thông tin vào biểu TK1 TS mới của bảo hiểm và mình đã dùng hàm vlookup thì điền gần như tất cả các thông tin được đầy đủ nhưng riêng phần địa chỉ thì cần điền rõ ràng các thông tin như :[TABLE="width: 693"]
[TR]
[TD="colspan: 18"][09]. Địa chỉ đăng ký hộ khẩu: [09.1]. Số nhà, đường phố, thôn xóm: …….………………[/TD]
[/TR]
[TR]
[TD="colspan: 3"][09.2]. Xã, phường …………[/TD]
[TD="colspan: 9"][09.3]. Quận, huyện ……….……[/TD]
[TD][/TD]
[TD="colspan: 4"][ 09.4]. Tỉnh, TP …….…[/TD]
[TD][/TD]
[/TR]
[/TABLE]
thì cần chia nhỏ và điền vào các ô tương ứng mong các bạn giúp đỡ và góp ý cho mình một hàm nào đó để có thể tách riêng từng phần địa chỉ của một người ! xin chân thanh cám ơn
File của mình ở link dưới các bạn giúp mình với nhé !
http://www.mediafire.com/view/cfpgqoama1dsfal/TK01ts.xlsx

 

[phihndhsp]

có file mới biết mặt mũi nó ra sao rồi mới tìm cách được bạn à

 

[banhbao]

cám ơn bạn đã quan tâm mình có file đính kèm theo link mediafile rồi! bạn giúp mình nhé ! cám ơn

 

[Hong.Van]

cám ơn bạn đã quan tâm mình có file đính kèm theo link mediafile rồi! bạn giúp mình nhé ! cám ơn

Cái này dùng Vlookup kết hợp 1 số hàm khác thì sẽ ra!
tôi gởi 1 file ví dụ, bạn tự tìm hiểu nhé!
Tại sheet MST.VN bạn thay đổi cell A4 thì sẽ thấy kết quả

 

[banhbao]

Cái này dùng Vlookup kết hợp 1 số hàm khác thì sẽ ra!
tôi gởi 1 file ví dụ, bạn tự tìm hiểu nhé!
Tại sheet MST.VN bạn thay đổi cell A4 thì sẽ thấy kết quả

cám ơn bản đã quan tam và góp ý nhưng dữ liệu của mình thì nó không ở từng ơ như của bạn ! mong bạn góp ý thêm Cám ơn bạn quan tâm

 

[banhbao]

có file mới biết mặt mũi nó ra sao rồi mới tìm cách được bạn à

cám ơn bạn quan tâm ngay dưới bài mình có link đính kèm bạn xem giúp mìnhn nhé

 

[banhbao]

Tại sheet dữ liệu của bạn phải làm ra 3 cột phụ để tách dữ liệu: Tỉnh/Thành phố ; HUyện ; Số nhà

Cột tỉnh (N2) kéo xuống cho hết:

N2     =TRIM(RIGHT(SUBSTITUTE(K2;"-";REPT(" ";255));255))

Cột huyện (O2) kéo xuống cho hết:

O2    =TRIM(LEFT(RIGHT(SUBSTITUTE(K2;"-";REPT(" ";255));510);255))

Cột số nhà (P2) kéo xuống cho hết:

P2     =TRIM(LEFT(K2;FIND("-";K2;1)-1))

Sau đó giống như cái bạn đã làm được mà vlookup tương ứng từng cột qua thôi.

rất cám ơn bạn đã góp ý mình đã chỉnh sửa lại cơ sở dữ liệu theo nguyên tắc chỉ đẻ lại đủ 3 tiêu chí : Xã/phường, Quận/huyện, Tỉnh/Thành phố sau đó mình vẫn gộp lại vào một ô nhất định và áp dụng các công thức sau :
Tìm xã:=TRIM(MID(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),1,LEN(VLOOKUP($Q$5,
Tìm Huyện: =TRIM(MID(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0))))
Tìm tỉnh :=TRIM(RIGHT(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0))))
công thức mình làm vậy tạm thời thì các kết quả của mình vẫn đúng không biết trong công thức có gì sai mong các bạn sửa chữa giúp ok

 

[Phuocam]

rất cám ơn bạn đã góp ý mình đã chỉnh sửa lại cơ sở dữ liệu theo nguyên tắc chỉ đẻ lại đủ 3 tiêu chí : Xã/phường, Quận/huyện, Tỉnh/Thành phố sau đó mình vẫn gộp lại vào một ô nhất định và áp dụng các công thức sau :
Tìm xã:=TRIM(MID(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),1,LEN(VLOOKUP($Q$5,
Tìm Huyện: =TRIM(MID(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0))))
Tìm tỉnh :=TRIM(RIGHT(SUBSTITUTE(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),"-",REPT(" ",LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0)))),LEN(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0))))
công thức mình làm vậy tạm thời thì các kết quả của mình vẫn đúng không biết trong công thức có gì sai mong các bạn sửa chữa giúp ok

Dùng hàm tự tạo thử xem sao.

Public Function DiaChi(Chuoi As String, Optional So As Byte = 4) As String
Dim i As Byte, Tam
Tam = Split("----" & Chuoi, "-")
i = UBound(Tam) + So - 4
DiaChi = Tam(i)
End Function

Cú pháp:
Tách số nhà: =DiaChi(Chuoi,1)
Tách Xã/phường : =DiaChi(Chuoi,2)
Tách Quận/huyện: =DiaChi(Chuoi,3)
Tách Tỉnh: =DiaChi(Chuoi,4)
Chuoi có thể là kết quả của hàm VLOOKUP
Ví dụ trong file, lấy tên tỉnh:
=DiaChi(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),4)

 

[banhbao]

Dùng hàm tự tạo thử xem sao.

Public Function DiaChi(Chuoi As String, Optional So As Byte = 4) As String
Dim i As Byte, Tam
Tam = Split("----" & Chuoi, "-")
i = UBound(Tam) + So - 4
DiaChi = Tam(i)
End Function

Cú pháp:
Tách số nhà: =DiaChi(Chuoi,1)
Tách Xã/phường : =DiaChi(Chuoi,2)
Tách Quận/huyện: =DiaChi(Chuoi,3)
Tách Tỉnh: =DiaChi(Chuoi,4)
Chuoi có thể là kết quả của hàm VLOOKUP
Ví dụ trong file, lấy tên tỉnh:
=DiaChi(VLOOKUP($Q$5,'DSCNV chuan '!$B$2:$X$1194,17,0),4)

hj cám ơn bạn quan tâm nhưng mình về VBA gà lắm ko biết gì mấy nên thấy ví dụ của bạn rất dễ hiểu nhưng ko biết áp dụng kiểu gì !

 

[Phuocam]

hj cám ơn bạn quan tâm nhưng mình về VBA gà lắm ko biết gì mấy nên thấy ví dụ của bạn rất dễ hiểu nhưng ko biết áp dụng kiểu gì !

Bạn có thể dùng cách copy số liệu thực của bạn paste sang file mẫu để thí nghiệm xem sao.

 

[banhbao]

Bạn có thể dùng cách copy số liệu thực của bạn paste sang file mẫu để thí nghiệm xem sao.

mình sữ thử cám ơn bạn nhé