Trich lọc năm sinh từ 1 chuỗi

NguoiVeTuLongDat · 27/7/11

Em đang cần lọc năm sinh như sau: Tại cột A có các dạng chuỗi sau:
A1 = Ông Nguyễn Văn A, sinh năm 1973
hoặc =Ông Nguyễn Văn B, sinh 73
Ông Nguyễn Văn C, sinh năm 1973ngụ tại
Ông Nguyễn Văn D, sinh năm 73ngụ tại
Ông Nguyễn Văn C, sinh năm 1973 ngụ tại
Ông Nguyễn Văn D, sinh năm 73 ngụ tại

Dùng công thức để điền năm sinh tại cột B là 1973.
Năm sinh < năm 2000

Làm bằng tay mệt quá, vì rất nhiều dòng.
Em xin cảm ơn

laianhtu · 27/7/11

NguoiVeTuLongDat đã viết:
Em đang cần lọc năm sinh như sau: Tại cột A có các dạng chuỗi sau:
A1 = Ông Nguyễn Văn A, sinh năm 1973
hoặc =Ông Nguyễn Văn B, sinh 73
Ông Nguyễn Văn C, sinh năm 1973ngụ tại
Ông Nguyễn Văn D, sinh năm 73ngụ tại
Ông Nguyễn Văn C, sinh năm 1973 ngụ tại
Ông Nguyễn Văn D, sinh năm 73 ngụ tại

Dùng công thức để điền năm sinh tại cột B là 1973.
Năm sinh < năm 2000

Làm bằng tay mệt quá, vì rất nhiều dòng.
Em xin cảm ơn

Bạn đưa file excel lên thì các Anh/chị mới có thể help nhanh chóng được !

phihndhsp · 27/7/11

NguoiVeTuLongDat đã viết:
Em đang cần lọc năm sinh như sau: Tại cột A có các dạng chuỗi sau:
A1 = Ông Nguyễn Văn A, sinh năm 1973
hoặc =Ông Nguyễn Văn B, sinh 73
Ông Nguyễn Văn C, sinh năm 1973ngụ tại
Ông Nguyễn Văn D, sinh năm 73ngụ tại
Ông Nguyễn Văn C, sinh năm 1973 ngụ tại
Ông Nguyễn Văn D, sinh năm 73 ngụ tại

Dùng công thức để điền năm sinh tại cột B là 1973.
Năm sinh < năm 2000

Làm bằng tay mệt quá, vì rất nhiều dòng.
Em xin cảm ơn

Bạn xem file này nha

huuthang_bd · 27/7/11

NguoiVeTuLongDat đã viết:
Em đang cần lọc năm sinh như sau: Tại cột A có các dạng chuỗi sau:
A1 = Ông Nguyễn Văn A, sinh năm 1973
hoặc =Ông Nguyễn Văn B, sinh 73
Ông Nguyễn Văn C, sinh năm 1973ngụ tại
Ông Nguyễn Văn D, sinh năm 73ngụ tại
Ông Nguyễn Văn C, sinh năm 1973 ngụ tại
Ông Nguyễn Văn D, sinh năm 73 ngụ tại

Dùng công thức để điền năm sinh tại cột B là 1973.
Năm sinh < năm 2000

Làm bằng tay mệt quá, vì rất nhiều dòng.
Em xin cảm ơn

Bạn dùng công thức mảng này:

Mã:

=RIGHT(19&MID($A1,MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1))),MAX(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))-MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))+1),4)

Bạn có thể đặt một name để công thức gọn hơn
Name

Mã:

VT=IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1))

Công thức

Mã:

=RIGHT(19&MID($A1,MIN(VT),MAX(VT)-MIN(VT)+1),4)

ndu96081631 · 27/7/11

huuthang_bd đã viết:
Bạn dùng công thức mảng này:

Mã:

=RIGHT(19&MID($A1,MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1))),MAX(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))-MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))+1),4)

Bạn có thể đặt một name để công thức gọn hơn
Name

Mã:

VT=IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1))

Công thức

Mã:

=RIGHT(19&MID($A1,MIN(VT),MAX(VT)-MIN(VT)+1),4)

Công thức này rất hay. Nó dựa vào việc tìm kiếm vị trí của các số từ 0 đến 9
Tuy nhiên dữ liệu phải chuẩn 1 loại mới được. Tác giả mà chơi kiểu này thì.. toi
Ông nguyễn văn C, sinh năm 1973 ngụ tại số 8 đường....

NguoiVeTuLongDat · 27/7/11

phihndhsp đã viết:
Bạn xem file này nha

Cám ơn bác Phihndhsp, công thức của bác rất ngắn gọn.
vẫn bị lỗi bác ạ, năm 73 phải ra kết quả là 1973 chứ không phải 73.
trường hợp ông Ngô văn Năm thì sao ạ, kết quả bị sai.

huuthang_bd đã viết:
Bạn dùng công thức mảng này:

Mã:

=RIGHT(19&MID($A1,MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1))),MAX(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))-MIN(IF(ISERROR(FIND(ROW($1:$10)-1,$A1)),"",FIND(ROW($1:$10)-1,$A1)))+1),4)

Cám ơn bạn, ra kết quả đúng; nhưng sao sao ấy, máy chạy chậm quá, chờ mãi mới ra kết quả. Sao em thử nhập lại công thức lại bị lỗi #VALUE.

huuthang_bd · 27/7/11

ndu96081631 đã viết:
Công thức này rất hay. Nó dựa vào việc tìm kiếm vị trí của các số từ 0 đến 9
Tuy nhiên dữ liệu phải chuẩn 1 loại mới được. Tác giả mà chơi kiểu này thì.. toi
Ông nguyễn văn C, sinh năm 1973 ngụ tại số 8 đường....

Thì em làm theo dữ liệu của tác giả mà. Nếu có gì phát sinh thì tác giả lại hỏi tiếp

NguoiVeTuLongDat · 27/7/11

ndu96081631 đã viết:
Công thức này rất hay. Nó dựa vào việc tìm kiếm vị trí của các số từ 0 đến 9
Tuy nhiên dữ liệu phải chuẩn 1 loại mới được. Tác giả mà chơi kiểu này thì.. toi
Ông nguyễn văn C, sinh năm 1973 ngụ tại số 8 đường....

Chết, thế thì chết, đúng là vậy đấy bác ạ. Có cách nào khắc phục không ạ?
Công thức của phihn thì đúng trường hợp này nhưng lại sai trường hợp khác.
Các bác giúp em với,

huuthang_bd · 27/7/11

NguoiVeTuLongDat đã viết:
Chết, thế thì chết, đúng là vậy đấy bác ạ. Có cách nào khắc phục không ạ?
Công thức của phihn thì đúng trường hợp này nhưng lại sai trường hợp khác.
Các bác giúp em với,

Vậy nó có quy luật gì không chứ. Có phải năm sinh luôn là dãy số đầu tiên trong chuỗi?

NguoiVeTuLongDat · 27/7/11

huuthang_bd đã viết:
Vậy nó có quy luật gì không chứ. Có phải năm sinh luôn là dãy số đầu tiên trong chuỗi?

Vâng, năm sinh xuất hiện đầu tiên, còn các dãy số phía sau không tính.

bebo021999 · 27/7/11

Thử công thức này xem:

Mã:

=LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5}))

ndu96081631 · 27/7/11

NguoiVeTuLongDat đã viết:
Vâng, năm sinh xuất hiện đầu tiên, còn các dãy số phía sau không tính.

Tạm sựa công thức của phihndhsp thành vầy đi:

PHP:

=RIGHT(19&IF(MID(A1,SEARCH("năm ",A1,1)+3,2)*1>2,MID(A1,SEARCH("năm",A1,1)+4,2),MID(A1,SEARCH("năm",A1,1)+3,5)),4)

bebo021999 · 27/7/11

ndu96081631 đã viết:
Tạm sựa công thức của phihndhsp thành vầy đi:

PHP:

=RIGHT(19&IF(MID(A1,SEARCH("năm ",A1,1)+3,2)*1>2,MID(A1,SEARCH("năm",A1,1)+4,2),MID(A1,SEARCH("năm",A1,1)+3,5)),4)

Không được sư phụ ơi, data này nhập không thống nhất kiểu : "1973ngụ tại" , "73ngụ tại", tức là mọi sai sót đều có thể, do đó không lọc theo chữ "năm" được, lỡ thay "sinh năm" bằng "năm sinh", hoặc "Trần Văn Năm", hoặc "Lê Văn An, 1973, ..." thì sai hết.
Bài này theo em, dùng LOOKUP tìm giá trị dãy số đầu tiên là được (bài #11)

NguoiVeTuLongDat · 27/7/11

bebo021999 đã viết:
Thử công thức này xem:

Mã:

=LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5}))

Cái này khắc phục được các lỗi xảy ra, nhưng năm sinh vẫn chưa quy về 4 ký tự bác ạ.
Công thức này ngắn gọn, chắc em sẽ dùng nó. Chắc phải dùng cột phụ để chuyển về 4 ký tự, có bác nào dúp dùm, file em đính kèm đây ạ.

ndu96081631 · 27/7/11

bebo021999 đã viết:
Không được sư phụ ơi, data này nhập không thống nhất kiểu : "1973ngụ tại" , "73ngụ tại", tức là mọi sai sót đều có thể, do đó không lọc theo chữ "năm" được, lỡ thay "sinh năm" bằng "năm sinh", hoặc "Trần Văn Năm", hoặc "Lê Văn An, 1973, ..." thì sai hết.
Bài này theo em, dùng LOOKUP tìm giá trị dãy số đầu tiên là được (bài #11)

Dữ liệu thì phải có cái gì chung để mà nhận biết. Qua các ví dụ, thấy rằng cell nào cũng có chữ "năm", vậy ta dựa vào từ khóa này để tách cũng hợp lý
Chứ dữ liệu tùm lum thì thậm chí công thức của bạn cũng... tèo... Ví dụ:
Ông nguyễn văn C, ngụ tại số 50 đường..., sinh năm 1973
----------------
Nhắn với tác giả: Để chắc chắn, tốt nhất bạn đưa dữ liệu thật lên đây, mất công mọi người đoán này đoán nọ lắm

bebo021999 · 27/7/11

NguoiVeTuLongDat đã viết:
Cái này khắc phục được các lỗi xảy ra, nhưng năm sinh vẫn chưa quy về 4 ký tự bác ạ.
Công thức này ngắn gọn, chắc em sẽ dùng nó. Chắc phải dùng cột phụ để chuyển về 4 ký tự, có bác nào dúp dùm, file em đính kèm đây ạ.

Nếu dùng cột phụ:

Mã:

=IF(B2<100,B2+1900,B2)

Nếu không dùng cột phụ, ghép luôn vào công thức chính, hơi dài (được voi phải chịu mất HBT):

Mã:

=LOOKUP(5000,IF(--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})<100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})+1900,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})))

huuthang_bd · 27/7/11

bebo021999 đã viết:
Nếu dùng cột phụ:

Mã:

=IF(B2<100,B2+1900,B2)

Nếu không dùng cột phụ, ghép luôn vào công thức chính, hơi dài (được voi phải chịu mất HBT):

Mã:

=LOOKUP(5000,IF(--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})<100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})+1900,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5})))

Sao không vầy cho nó gọn:

Mã:

=RIGHT(19&LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5})),4)

bebo021999 · 27/7/11

huuthang_bd đã viết:
Sao không vầy cho nó gọn:

Mã:

=RIGHT(19&LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5})),4)

Hay lắm huuthang, minh đang bí cái vụ cộng thêm 1900 vô, không ngờ chỉ là ghép chuỗi thôi. Thanks nhiều nhé, học thêm được nhiều thứ.

Trich lọc năm sinh từ 1 chuỗi

Thành viên chính thức

Thành viên tiêu biểu

Thành viên gạo cội

File đính kèm

Chuyên gia GPE

Huyền thoại GPE

Thành viên chính thức

Chuyên gia GPE

Thành viên chính thức

Chuyên gia GPE

Thành viên chính thức

Thành viên gạo cội

Huyền thoại GPE

Thành viên gạo cội

Thành viên chính thức

File đính kèm

Huyền thoại GPE

Thành viên gạo cội

Chuyên gia GPE

Thành viên gạo cội

Đếm ngược thời gian