Làm thế nào để tách số và chữ thành các cột riêng biệt

[phucds94]

Chào các anh/chị
Anh/Chị cho em xin công thức e tách dữ liệu như thế này giúp e với ạ:

Danh sách	số CCCD	Ngày cấp	Họ tên
03419500282729072016 - MAI THỊ NỤ	034195002827	29072016	MAI THỊ NỤ
151733203319032004 - NGUYỄN THỊ NHÂN
....

Em xin chân thành cảm ơn ạ!

 

[cantl]

THỊ

Chỉ dùng cho 2 loại dữ liệu này nhé.

=IF(B$1="số CCCD",LEFT(A2,FIND(C2,A2)),MID($A2,FIND(" - ",$A2)-IFERROR(1/(1/((LEN(B$1)=8)*8)),-3),IFERROR(1/(1/((LEN(B$1)=8)*8)),100)))
→
=IF(B$1="số CCCD",LEFT(A2,FIND(C2,A2)-1),MID($A2,FIND(" - ",$A2)-IFERROR(1/(1/((LEN(B$1)=8)*8)),-3),IFERROR(1/(1/((LEN(B$1)=8)*8)),100)))

 

[hoangtuan.net]

Chỉ dùng cho 2 loại dữ liệu này nhé.

=IF(B$1="số CCCD",LEFT(A2,FIND(C2,A2)),MID($A2,FIND(" - ",$A2)-IFERROR(1/(1/((LEN(B$1)=8)*8)),-3),IFERROR(1/(1/((LEN(B$1)=8)*8)),100)))

Công thức của bác hay quá!

1. IF(D$1="số CCCD";...;...): Nếu giá trị trong ô D1 là “số CCCD”, thì thực hiện công thức sau đó, nếu không thì thực hiện công thức khác.
2. LEFT(C2;FIND(E2;C2)): Nếu giá trị trong ô D1 là “số CCCD”, thì trả về một chuỗi bắt đầu từ bên trái của ô C2, với độ dài là vị trí của chuỗi trong ô E2 trong ô C2.
3. MID($A2;FIND(" - ";$A2)-IFERROR(1/(1/((LEN(D$1)=8)*8));-3);IFERROR(1/(1/((LEN(D$1)=8)*8));100)): Nếu giá trị trong ô D1 không phải là “số CCCD”, thì trả về một chuỗi từ ô A2. Vị trí bắt đầu của chuỗi là vị trí của chuỗi " - " trong ô A2 trừ đi một giá trị. Giá trị này là kết quả của hàm IFERROR, nếu LEN(D$1) bằng 8 thì trả về 8, nếu không thì trả về -3. Độ dài của chuỗi cũng được xác định bởi một hàm IFERROR tương tự.

 

[cantl]

Công thức của bác hay quá!

1. IF(D$1="số CCCD";...;...): Nếu giá trị trong ô D1 là “số CCCD”, thì thực hiện công thức sau đó, nếu không thì thực hiện công thức khác.
2. LEFT(C2;FIND(E2;C2)): Nếu giá trị trong ô D1 là “số CCCD”, thì trả về một chuỗi bắt đầu từ bên trái của ô C2, với độ dài là vị trí của chuỗi trong ô E2 trong ô C2.
3. MID($A2;FIND(" - ";$A2)-IFERROR(1/(1/((LEN(D$1)=8)*8));-3);IFERROR(1/(1/((LEN(D$1)=8)*8));100)): Nếu giá trị trong ô D1 không phải là “số CCCD”, thì trả về một chuỗi từ ô A2. Vị trí bắt đầu của chuỗi là vị trí của chuỗi " - " trong ô A2 trừ đi một giá trị. Giá trị này là kết quả của hàm IFERROR, nếu LEN(D$1) bằng 8 thì trả về 8, nếu không thì trả về -3. Độ dài của chuỗi cũng được xác định bởi một hàm IFERROR tương tự.

Công thức này mang tính giải trí là chính, nếu may mắn thì tác giả sẽ gặp được hàm gọn hơn.

 

[befaint]

Hay quá kiểu gì mà kết quả sai bét tè lè nhè.



@Thớt:

B2=LEFT(TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500)),500)),LEN(TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500)),500)))-8)
C2=RIGHT(TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500)),500)),8)
D2=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",500)),500))

 

[anhtuanle123]

đơn giản như vầy thui
B1=SUBSTITUTE(LEFT(SUBSTITUTE(A1,"-","--------------------------------------------------"),50),"-","")
C1=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"-","--------------------------------------------------"),50),"-","")

giải pháp !
cho nó dài ra như vầy
rùi cắt trái phải thôi !
03419500282729072016 -------------------------------------------------- MAI THỊ NỤ

 

[VetMini]

Bài này có cả đống cách giải.
Vấn đề quan trọng là tránh các con toán lặp lại không cần thiết.

Một trong những giải pháp hiệu quả là tính ra chỗ nào thì trừ đi để tính cái kế tiếp. Muốn làm như vậy thì phải tìm chỗ cứng tính trước, các chỗ mềm tính sau.
- Trong chuỗi ban đầu, chỉ có tên là chuỗi cứng nhắc (không thể thay đổi dạng), Như vậy ta có thể dùng phương pháp hàm tách chuỗi để tính cột họ tên trước.
- Đem chuỗi ban đầu, trừ đi dộ dài họ tên + 3 thì ra chuỗi số.
- 8 ký tự cuối là ngày tháng
- chỗ còn lại là cai mã gì đó.

Một trong những thói quen của người ta là tìm cách tính từ trái sang phải. Bài này cần tư duy rộng hơn, điển hình là giải pháp trên tính từ phải sang trái.

 

[Phan Thế Hiệp]

Bài này có cả đống cách giải.
Vấn đề quan trọng là tránh các con toán lặp lại không cần thiết.

Một trong những giải pháp hiệu quả là tính ra chỗ nào thì trừ đi để tính cái kế tiếp. Muốn làm như vậy thì phải tìm chỗ cứng tính trước, các chỗ mềm tính sau.
- Trong chuỗi ban đầu, chỉ có tên là chuỗi cứng nhắc (không thể thay đổi dạng), Như vậy ta có thể dùng phương pháp hàm tách chuỗi để tính cột họ tên trước.
- Đem chuỗi ban đầu, trừ đi dộ dài họ tên + 3 thì ra chuỗi số.
- 8 ký tự cuối là ngày tháng
- chỗ còn lại là cai mã gì đó.

Một trong những thói quen của người ta là tìm cách tính từ trái sang phải. Bài này cần tư duy rộng hơn, điển hình là giải pháp trên tính từ phải sang trái.

Vầy hả anh:

D2=TRIM(MID(A2,FIND("-",A2)+1,50))
C2=RIGHT(TRIM(LEFT(A2,FIND("-",A2)-1)),8)
B2=LEFT(A2,FIND(C2,A2)-1)

 

[HieuCD]

Vầy hả anh:

D2=TRIM(MID(A2,FIND("-",A2)+1,50))
C2=RIGHT(TRIM(LEFT(A2,FIND("-",A2)-1)),8)
B2=LEFT(A2,FIND(C2,A2)-1)

Có khi nào ngày tháng năm ô C2 ngẫu nhiên trùng với 8 số trong các số CCCD không

 

[Phan Thế Hiệp]

Có khi nào ngày tháng năm ô C2 ngẫu nhiên trùng với 8 số trong các số CCCD không

Nói theo "xu hướng (trend)" mấy đứa bây giờ: "Hên xui anh ơi!"

Xửa xưa, thì biết CCCP, hay PCCC, hay CÔCC, còn giờ thì CCCD, nghe đồn chỉ còn CC, tha hồ cho trí tưởng tượng bay bổng theo các từ viết tắt này, ngộ nhỡ dùng bậy, người ta nói mình văng tục thì khổ đời!

Anh em mình hết cửa biết rồi!

Khà khà khà

 

[phucds94]

Em cảm ơn mọi người ạ!

 

[VetMini]

Có khi nào ngày tháng năm ô C2 ngẫu nhiên trùng với 8 số trong các số CCCD không

Đó là lỗi của thớt. Tính sau.

...
Xửa xưa, thì biết CCCP, ...

Hồi thời của tôi, nó gọi là Thẻ Căn Cước.
Đi đường có vùng còn bắt phải đem theo bản sao Sổ Gia Cư.
Sau lại thêm mọt mớ giấy tờ.
Một thằng con trai đi đường xách theo hai cái trên, cùng giấy tờ hoãn dịch (tạm hoãn nhập ngũ), cộng giấy chứng nhận của Phường là đang thi hành tốt nhiệm vụ Nhân Dân Tự Vệ,... Ôi thôi đủ thứ giấy tờ, bất cứ cái gì mấy ông quân cảnh nghĩ ra.

Nếu thiếu một vài cái trên, nhất là giấy tờ hoãn dịch thì phải xách theo chỉ vàng hoặc vài đức thánh (Trần Hưng Đạo, một tờ mệnh giá 500 đồng, 3-4 tờ xấp xỉ một chỉ). Để cho mấy ông tóm cổ tống lên trung tâm 3 (nơi nhập ngũ) thì giá chuộc ra cao lắm.

 

[Phan Thế Hiệp]

Hồi thời của tôi, nó gọi là Thẻ Căn Cước.
Đi đường có vùng còn bắt phải đem theo bản sao Sổ Gia Cư.
Sau lại thêm mọt mớ giấy tờ.
Một thằng con trai đi đường xách theo hai cái trên, cùng giấy tờ hoãn dịch (tạm hoãn nhập ngũ), cộng giấy chứng nhận của Phường là đang thi hành tốt nhiệm vụ Nhân Dân Tự Vệ,... Ôi thôi đủ thứ giấy tờ, bất cứ cái gì mấy ông quân cảnh nghĩ ra.

"Đi quân dịch là thương nòi giống..." mà anh! (Bức tâm thư - Lam Phương)

đức thánh (Trần Hưng Đạo, một tờ mệnh giá 500 đồng, 3-4 tờ xấp xỉ một chỉ

Em thì chỉ khoái tờ "mấy con voi" hà! Tết mà được lì xì tờ này là mừng hết lớn!

 

[VetMini]

Em thì chỉ khoái tờ "mấy con voi" hà! Tết mà được lì xì tờ này là mừng hết lớn!

Lục bát dân gian:
Bao giờ lúa mọc lên chì
Voi đi trên giấy thầy tăng mới về.

1. Đồng bạc cắc hồi xưa (Tây phát hành) có hình bó lúa
2. Tờ giấy 5 đồng (cũng thời Tây) có hình con voi - đờn ông đến tuổi đòng thuế thân thời ấy cũng 5 đồng
3. thầy tăng nói lái lại là thằng tây
4. Nghe nói sau khi phát hành 2 mệnh giá này thì Tây thua, rút về nước.

 

[ptm0412]

Có khi nào ngày tháng năm ô C2 ngẫu nhiên trùng với 8 số trong các số CCCD không

Nếu dữ liệu đúng như hình minh họa bài 1 thì trùng chả sao cả. Luôn luôn lấy 8 số bên phải làm ngày tháng, còn bao nhiêu bên trái là số CC, trùng cũng nằm trong khúc đầu, kệ nó.

 

[Phan Thế Hiệp]

C2 ngẫu nhiên trùng với 8 số trong các số CCCD không

Ý anh nói lỡ CCCD nó như vầy:
"119032004519032004 - VŨ NHƯ CẨN"
công thức B2=LEFT(A2,FIND(C2,A2)-1) còn hớ hênh phải không?

Vậy vầy cho chắc chút:
B2=LEFT(A2,FIND("-",SUBSTITUTE(A2," ",""))-9)

 

[VetMini]

B2 = Left(A2, Len(A2) - Len(C2) - 11)
(lúc đầu, ý tôi nói "phải qua trái" là chỗ này đây)