Cần giúp: Lại là bài tách địa chỉ ra từng phần bằng công thức

[xuongrongdat]

Nhờ mọi người làm giúp mình bài này bằng cả 2 cách: Text to Columns và công thức.
Do dữ liệu có nhiều loại: loại đủ 5 trường (số nhà, đường, phường, huyện, tỉnh), loại 4 trường, 3 trường. Chúng cách nhau bằng dấu phẩy.
Mình minh họa bằng file đính kèm như sau.



Xin cảm ơn.

 

[befaint]

Điền cho đủ 4 dấu phẩy rồi làm. Vậy là lèo cái xong.

 

[Binbo2020]

Mình minh họa bằng file đính kèm như sau.

Dạng bài này nhiều lắm rồi mà sao không chịu tìm nhỉ

Bài đã được tự động gộp: 19/10/21

Kéo cả bảng

C3=IFERROR(TRIM(MID(SUBSTITUTE(","&$B3;",";REPT(" ";99));(COLUMN(A3)+(LEN($B3)-LEN(SUBSTITUTE($B3;",";""))-4))*99;99));"")

 

[Nguyenhoangphong0902]

Cho bác file này

 

[xuongrongdat]

Điền cho đủ 4 dấu phẩy rồi làm. Vậy là lèo cái xong.

Con chưa hiểu ý bác.

Dạng bài này nhiều lắm rồi mà sao không chịu tìm nhỉ

Bài đã được tự động gộp: 19/10/21

Kéo cả bảng

C3=IFERROR(TRIM(MID(SUBSTITUTE(","&$B3;",";REPT(" ";99));(COLUMN(A3)+(LEN($B3)-LEN(SUBSTITUTE($B3;",";""))-4))*99;99));"")

Cho bác file này

Cảm ơn 2 bạn rất nhiều. Rất đúng ý mình luôn.

 

[befaint]

Cột phụ cho cả công thức và Text to Columns.
C3=REPT(",",4-LEN(B3)+LEN(SUBSTITUTE(B3,",","")))&B3

Công thức dùng cho cột phụ:
=TRIM(MID(SUBSTITUTE($C3,",",REPT(" ",500)),(COLUMN(A$1)-1)*500+1,400))

 

[HUONGHCKT]

Nhờ mọi người làm giúp mình bài này bằng cả 2 cách: Text to Columns và công thức.
Do dữ liệu có nhiều loại: loại đủ 5 trường (số nhà, đường, phường, huyện, tỉnh), loại 4 trường, 3 trường. Chúng cách nhau bằng dấu phẩy.
Mình minh họa bằng file đính kèm như sau.
View attachment 267953


Xin cảm ơn.

Có 3 hay 4, hay 5 trường cũng được , Nếu dữ liệu ở ô B ... đều có địa danh tỉnh ở cuối cùng, lùi tiếp là địa danh cấp huyện thị, lùi tiếp là cấp phường xã
thì có thể thử dùng code này xem sao.

Sub TACH()
Dim i&, j&, Lr&, k&, R&, C&
Dim Arr(), KQ(), Temp
With Sheet1
Lr = .Cells(Rows.Count, 2).End(xlUp).Row
Arr = .Range("B3:B" & Lr).Value
R = UBound(Arr)
ReDim KQ(1 To R, 1 To 5)
    For i = 1 To R
    k = 6
        Temp = Split(Arr(i, 1), ",")
            C = UBound(Temp)
                For j = C + 1 To 1 Step -1
                    k = k - 1
                KQ(i, k) = Temp(j - 1)
                Next j
    Next i
.Range("I3").Resize(R, 5) = KQ
End With
End Sub

 

[xuongrongdat]

Cột phụ cho cả công thức và Text to Columns.
C3=REPT(",",4-LEN(B3)+LEN(SUBSTITUTE(B3,",","")))&B3

Công thức dùng cho cột phụ:
=TRIM(MID(SUBSTITUTE($C3,",",REPT(" ",500)),(COLUMN(A$1)-1)*500+1,400))

Con làm mà nó chưa có ra.

Có 3 hay 4, hay 5 trường cũng được , Nếu dữ liệu ở ô B ... đều có địa danh tỉnh ở cuối cùng, lùi tiếp là địa danh cấp huyện thị, lùi tiếp là cấp phường xã
thì có thể thử dùng code này xem sao.

Sub TACH()
Dim i&, j&, Lr&, k&, R&, C&
Dim Arr(), KQ(), Temp
With Sheet1
Lr = .Cells(Rows.Count, 2).End(xlUp).Row
Arr = .Range("B3:B" & Lr).Value
R = UBound(Arr)
ReDim KQ(1 To R, 1 To 5)
    For i = 1 To R
    k = 6
        Temp = Split(Arr(i, 1), ",")
            C = UBound(Temp)
                For j = C + 1 To 1 Step -1
                    k = k - 1
                KQ(i, k) = Temp(j - 1)
                Next j
    Next i
.Range("I3").Resize(R, 5) = KQ
End With
End Sub

Code chạy ngon bạn ơi. Nhưng đúng với <= 5 trường dữ liệu. Cảm ơn bạn.
Mình thêm vào 2 trường "Khu phố" và "Tổ" nữa thì code nó bị lỗi Out of range á.

 

[befaint]

Con làm mà nó chưa có ra.

Chí ít chụp cái hình xinh xinh đã làm như thế nào lên chứ.

 

[xuongrongdat]

Chí ít chụp cái hình xinh xinh đã làm như thế nào lên chứ.

Nó bị vầy nè bác:
...

 

[befaint]

Nó bị vầy nè bác:

Bó tay chấm com luôn.

Chụp cái đã làm = chụp cái ô chứa công thức.

Mà khi thử thì cứ thử y chang người ta đã làm đó. Cột phụ là cột C. Tự ý đổi thành cột i nhưng chắc công thức sau chưa đổi sang cột i.

 

[xuongrongdat]

Bó tay chấm com luôn.

Chụp cái đã làm = chụp cái ô chứa công thức.

Mà khi thử thì cứ thử y chang người ta đã làm đó. Cột phụ là cột C. Tự ý đổi thành cột i nhưng chắc công thức sau chưa đổi sang cột i.

View attachment 267984

Xin lỗi bác nãy sao tự nhiên cắt mất dòng công thức bên trên. Để con thử lại xem sao. Tại con chưa hiểu ý bác. Bài #6 của bác con đọc đi đọc lại vẫn thấy ù ù cạc cạc sao á.

 

[VetMini]

Nó bị vầy nè bác:
...

Muốn làm được cái mà tác giả bài #2 chỉ thì bạn phải luyện được khả năng"pattern recognition" lúc đọc dữ liệu.
Tác giả bài #2 rõ ràng đọc một mớ dữ liệu của bạn và nhận ra cáí pattern là có từ 2 đến 4 dấu phẩy. Như vậy muốn text-to-columns thì thêm sao cho đủ 4. Nhiệm vụ của cột phụ là vậy.

Bản thân tôi thì nghĩ là bạn đã đến tuổi nên giã từ ba cái mớ phim Tầu/Hong Kong và truyện chưởng. Để thìn giờ và đầu óc học cách đọc dữ liệu gầy dựng cái kỹ năng "pattern recognition" tốt hơn. Truyện và phim Tầu có khuynh hướng dùng triết lý ba phải (chính chả ra chính, tài chả ra tà), không có lợi cho người làm việc với dữ liệu.

Bạn lưu ý sẽ biết rằng tác giả bài #2 vẫn hay phải nhắc nhở những người đọc bài trả lời bằng cách lướt nhanh và với đầu óc đầy định kiến: "đọc thật chậm và suy gẫm cho kỹ"

(*1) Trong truyện Tầu, tôi chỉ thấy Gia Cát Thanh Vân (điẻn hình Tuyết Sơn Kỳ Nữ) là triết lý rõ rệt nhất. Tuy rất sến, nhưng cứ tà là tà, và chính là chính).

 

[befaint]

công thức

Đừng Ctrl + H công thức. Mà cái này tự mình dò đi.

 

[HUONGHCKT]

Con làm mà nó chưa có ra.

Code chạy ngon bạn ơi. Nhưng đúng với <= 5 trường dữ liệu. Cảm ơn bạn.
Mình thêm vào 2 trường "Khu phố" và "Tổ" nữa thì code nó bị lỗi Out of range á.

Bị lỗi bởi vị khai báo kích thước cho mảng kết quả là 5 thôi mà. bạn thử khai báo redim KQ( 1 to R, 1 to M) với M= số trường lớn nhất có trong cột B (ví dụ : Tổ =1, ấp=2,thôn=3, xóm=4, xã=5,huyện=6, tỉnh là 7) và thay đổi lại chỗ
ReDim KQ(1 To R, 1 To M)
For i = 1 To R
k = 6 chỗ này thay bằng k=M+1 và chạy code thử xem.

 

[Hoàng Tuấn 868]

Nhờ mọi người làm giúp mình bài này bằng cả 2 cách: Text to Columns và công thức.
Do dữ liệu có nhiều loại: loại đủ 5 trường (số nhà, đường, phường, huyện, tỉnh), loại 4 trường, 3 trường. Chúng cách nhau bằng dấu phẩy.
Mình minh họa bằng file đính kèm như sau.
Xin cảm ơn.

Góp vui cùng Xuongrongdat.

 

[xuongrongdat]

Bị lỗi bởi vị khai báo kích thước cho mảng kết quả là 5 thôi mà. bạn thử khai báo redim KQ( 1 to R, 1 to M) với M= số trường lớn nhất có trong cột B (ví dụ : Tổ =1, ấp=2,thôn=3, xóm=4, xã=5,huyện=6, tỉnh là 7) và thay đổi lại chỗ
ReDim KQ(1 To R, 1 To M)
For i = 1 To R
k = 6 chỗ này thay bằng k=M+1 và chạy code thử xem.

Ngon lành rồi. Cảm ơn bạn nhiều.

Góp vui cùng Xuongrongdat.

Ngon luôn. Cảm ơn bạn nhiều.

 

[longtay1111]

Điền cho đủ 4 dấu phẩy rồi làm. Vậy là lèo cái xong.

Số lượng dấu "," trong chuỗi tối đa là 5, do vậy phải bổ sung mỗi dòng thêm số lượng dấu''," sao cho bằng 5. Khi các dòng đều có 5 dấu "," thì dùng Text Column là xong, không phải dùng công thức nữa.

 

[befaint]

Số lượng dấu "," trong chuỗi tối đa là 5

Số 5 đẹp.

 

[Hoàng Tuấn 868]

Số lượng dấu "," trong chuỗi tối đa là 5, do vậy phải bổ sung mỗi dòng thêm số lượng dấu''," sao cho bằng 5. Khi các dòng đều có 5 dấu "," thì dùng Text Column là xong, không phải dùng công thức nữa.

Như vậy chỉ phải lập công thức điền dấu phẩy thôi bạn nhỉ.