Lấy một chuỗi số trong một text - sử dụng hàm find() và mid()

Liên hệ QC
Tôi tuân thủ nội quy khi đăng bài
Có
W

Wanderman

Thành viên mới
Tham gia
30/6/08
Bài viết
47
Được thích
8
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
 

File đính kèm

Wanderman đã viết:
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
Nhấp chuột vào đây để mở rộng...
Hàm này lấy 7 ký tự, tính từ ký tự là số đầu tiên sau chữ ID.
Mã: 
=MID(B14,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B14,FIND("ID",B14)+2),100)),7)
 
Wanderman đã viết:
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
Nhấp chuột vào đây để mở rộng...
Bạn thử công thức này xem có đúng ko nhé

B2=IFERROR(MID(LEFT(MID(B2,SEARCH("ID",B2)+2,50),SEARCH("-",MID(B2,SEARCH("ID",B2)+2,50))-1),MIN(IFERROR(FIND(ROW($1:$10)-1,LEFT(MID(B2,SEARCH("ID",B2)+2,50),SEARCH("-",MID(B2,SEARCH("ID",B2)+2,50))-1)),"")),10),MID(B2,SEARCH("ID",B2)+2,50))
 

File đính kèm

Wanderman đã viết:
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
Nhấp chuột vào đây để mở rộng...
1 công thức nữa tham khảo A2:

Mã: 
=LOOKUP(10000000,--MID(MID(B2,FIND("ID",B2,1)+2,10),MIN(FIND({0;1;2;3;4;5;6;7;8;9},MID(B2,FIND("ID",B2,1)+2,10)&1234567890)),ROW(INDIRECT("1:"&LEN(MID(B2,FIND("ID",B2,1)+2,10))))))
 
longtay1111 đã viết:
Bạn thử công thức này xem có đúng ko nhé

B2=IFERROR(MID(LEFT(MID(B2,SEARCH("ID",B2)+2,50),SEARCH("-",MID(B2,SEARCH("ID",B2)+2,50))-1),MIN(IFERROR(FIND(ROW($1:$10)-1,LEFT(MID(B2,SEARCH("ID",B2)+2,50),SEARCH("-",MID(B2,SEARCH("ID",B2)+2,50))-1)),"")),10),MID(B2,SEARCH("ID",B2)+2,50))
Nhấp chuột vào đây để mở rộng...
Bác cho em hỏi con số 50 ở trong hàm Search ý nghĩa của nó là gì bác nhỉ?
 
Cám ơn các bác nhiều nhưng em thử áp dụng vào dữ liệu của em thì chưa ổn ạ!

Công thức mảng của bác @longtay1111 có thể giải thích kỹ hơn giúp em được không ạ? Ví dụ như đoạn: MIN(IFERROR(FIND(ROW($1:$10)-1; hay đoạn hàm SEARCH có con số 50 là có ý nghĩa gì được không ạ?
 
Wanderman đã viết:
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
Nhấp chuột vào đây để mở rộng...
Athử coong thức này
Mã: 
=RIGHT(TRIM(LEFT(SUBSTITUTE(MID(B2,SEARCH("ID",B2)+2,LEN(B2)),"-",REPT(" ",LEN(B2))),LEN(B2))),7)
hoặc
=MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17,SEARCH("ID",B2)+2)),7)
 
Lần chỉnh sửa cuối:
Wanderman đã viết:
Cám ơn các bác nhiều nhưng em thử áp dụng vào dữ liệu của em thì chưa ổn ạ!

Công thức mảng của bác @longtay1111 có thể giải thích kỹ hơn giúp em được không ạ? Ví dụ như đoạn: MIN(IFERROR(FIND(ROW($1:$10)-1; hay đoạn hàm SEARCH có con số 50 là có ý nghĩa gì được không ạ?
Nhấp chuột vào đây để mở rộng...
Số 50 đấy của hàm MID;
Công thức mảng phải kết thúc = Ctrl + Shift + Enter;
Mấy tham số trong trong công thức thì bạn gõ F1 trong excel rồi đọc;
 
Wanderman đã viết:
Em có danh sách gồm 1 chuỗi ký tự ở cột B, em muốn lấy 1 chuỗi ký tự (cột A) gồm 7 con số (ID) đằng sau chữ ID, nhưng khó ở chỗ sau từ ID thì lúc là không có ký tư hoặc ký tự trống nào, lúc có ký tự lúc có 2 đến 3 ký tự.....

Chỉ có quy luật duy nhất là phải lấy 7 ký tự số sau chữ ID trong text tương ứng từ cột B.

Tình hình là em đang chưa biết cách lập công thức như nào hay phải dùng VBA. Nhờ các cao thủ giúp cho ạ!
Nhấp chuột vào đây để mở rộng...
Bạn thử dùng công thức này xem nhé, mình thấy OK á.
=MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7)
 
hoangtran1176 đã viết:
Bạn thử dùng công thức này xem nhé, mình thấy OK á.
=MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7)
Nhấp chuột vào đây để mở rộng...
Vẫn không được bác ạ! Có lúc sau ID nó còn dấu :, / hoặc cả vài chữ nữa...nếu dùng SEARCH hay FIND thì Start_num có lúc lên đến 7-8 ký tự mới đến phần 7 chữ số em muốn lấy!
 
Wanderman đã viết:
Vẫn không được bác ạ! Có lúc sau ID nó còn dấu :, / hoặc cả vài chữ nữa...nếu dùng SEARCH hay FIND thì Start_num có lúc lên đến 7-8 ký tự mới đến phần 7 chữ số em muốn lấy!
Nhấp chuột vào đây để mở rộng...
Ủa bạn thử lại xem chứ hàm mình dùng dù sau ID có bất kỳ ký tự gì cũng không bị ảnh hưởng mà.
Bài đã được tự động gộp:

Wanderman đã viết:
Vẫn không được bác ạ! Có lúc sau ID nó còn dấu :, / hoặc cả vài chữ nữa...nếu dùng SEARCH hay FIND thì Start_num có lúc lên đến 7-8 ký tự mới đến phần 7 chữ số em muốn lấy!
Nhấp chuột vào đây để mở rộng...
=(MID(B2,SEARCH("ID",B2),13)&"-") =>ID5427555-GCN-
=SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7 => 3.
=+MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7) =>5427555.
 
Lần chỉnh sửa cuối:
hoangtran1176 đã viết:
Ủa bạn thử lại xem chứ hàm mình dùng dù sau ID có bất kỳ ký tự gì cũng không bị ảnh hưởng mà.
Bài đã được tự động gộp:


=(MID(B2,SEARCH("ID",B2),13)&"-") =>ID5427555-GCN-
=SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7 => 3.
=+MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7) =>5427555.
Nhấp chuột vào đây để mở rộng...
Gần được rồi bác!

Bác xử lý nốt cho em trường hợp cuối bôi vàng với ạ!

Cám ơn bác nhiều.
hoangtran1176 đã viết:
Ủa bạn thử lại xem chứ hàm mình dùng dù sau ID có bất kỳ ký tự gì cũng không bị ảnh hưởng mà.
Bài đã được tự động gộp:


=(MID(B2,SEARCH("ID",B2),13)&"-") =>ID5427555-GCN-
=SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7 => 3.
=+MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7) =>5427555.
Nhấp chuột vào đây để mở rộng...
Cám ơn bác rất nhiều!

Nếu được bác xử lý giúp em trường hợp cuối (bôi vàng) với ạ! Nó chỉ lấy 6 số chứ không lấy đủ 7 chữ số.
 

File đính kèm

Cách này dễ hiểu thật đấy, bác nghĩ ra được thêm nối dấu &"-" hay thật, không nối là có cái bị lỗi :)
hoangtran1176 đã viết:
Ủa bạn thử lại xem chứ hàm mình dùng dù sau ID có bất kỳ ký tự gì cũng không bị ảnh hưởng mà.
Bài đã được tự động gộp:


=(MID(B2,SEARCH("ID",B2),13)&"-") =>ID5427555-GCN-
=SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7 => 3.
=+MID((MID(B2,SEARCH("ID",B2),13)&"-"),SEARCH("-",(MID(B2,SEARCH("ID",B2),13)&"-"))-7,7) =>5427555
Nhấp chuột vào đây để mở rộng...
Bài đã được tự động gộp:

Wanderman đã viết:
Gần được rồi bác!

Bác xử lý nốt cho em trường hợp cuối bôi vàng với ạ!

Cám ơn bác nhiều.

Cám ơn bác rất nhiều!

Nếu được bác xử lý giúp em trường hợp cuối (bôi vàng) với ạ! Nó chỉ lấy 6 số chứ không lấy đủ 7 chữ số.
Nhấp chuột vào đây để mở rộng...
Bác sửa số 13 thành số 14 là được nhé
 
Wanderman đã viết:
Gần được rồi bác!

Bác xử lý nốt cho em trường hợp cuối bôi vàng với ạ!

Cám ơn bác nhiều.

Cám ơn bác rất nhiều!

Nếu được bác xử lý giúp em trường hợp cuối (bôi vàng) với ạ! Nó chỉ lấy 6 số chứ không lấy đủ 7 chữ số.
Nhấp chuột vào đây để mở rộng...
=MID((MID(B17,SEARCH("ID",B17),14)&"-"),SEARCH("-",(MID(B17,SEARCH("ID",B17),14)&"-"))-7,7), thay số 13 bằng số 14 nha
Mình nghĩ chắc bạn cũng chưa hiểu về cái hàm mình viết, nếu hiểu logic thì không hỏi câu này hihi =))
 
Wanderman đã viết:
Gần được rồi bác!

Bác xử lý nốt cho em trường hợp cuối bôi vàng với ạ!

Cám ơn bác nhiều.

Cám ơn bác rất nhiều!

Nếu được bác xử lý giúp em trường hợp cuối (bôi vàng) với ạ! Nó chỉ lấy 6 số chứ không lấy đủ 7 chữ số.
Nhấp chuột vào đây để mở rộng...
Bạn dùng thêm hàm SUBSTITUTE() để xử lý hết khoàng trắng trong chuỗi gốc là ok nhé:

=MID((MID(SUBSTITUTE(B2," ",""),SEARCH("ID",SUBSTITUTE(B2," ","")),13)&"-"),SEARCH("-",(MID(SUBSTITUTE(B2," ",""),SEARCH("ID",SUBSTITUTE(B2," ","")),13)&"-"))-7,7)
 

File đính kèm

Một cách dùng UDF:
Nếu chuỗi tại B2, tại D2 nhập:
Mã: 
=TimID(B2,7)
Với 7 là số ký số cần tìm (Có thể điều chỉnh thành 5,6,7,8 theo ý muốn)
Cách dùng:
Chuột phải tên sheet, viewCode. insert module rồi dán code này vào. Lưu dạng .xlsm
PHP: 
Option Explicit
Function TimID(ByVal cell As Range, Optional num As Integer)
Dim i&, id As String, st As String
If InStr(1, cell, "ID") = 0 Then
    TimID = "Khong tim thay"
    Exit Function
End If
st = Replace(Replace(Mid(cell, InStr(1, cell, "ID") + 2, 255), " ", ""), ".", "")
For i = 1 To Len(st) - num + 1
    id = Mid(st, i, num)
    If IsNumeric(id) Then
        TimID = id
        Exit Function
    End If
Next
End Function
Capture.JPG
 

File đính kèm

Cám ơn các bác rất nhiều!

Nhờ các bác mà em đã xử lý được vấn đề của dữ liệu của em.

GPE chưa bao giờ làm em thất vọng!
 
bebo021999 đã viết:
Một cách dùng UDF:
Nếu chuỗi tại B2, tại D2 nhập:
Mã: 
=TimID(B2,7)
Với 7 là số ký số cần tìm (Có thể điều chỉnh thành 5,6,7,8 theo ý muốn)
Cách dùng:
Chuột phải tên sheet, viewCode. insert module rồi dán code này vào. Lưu dạng .xlsm
PHP: 
Option Explicit
Function TimID(ByVal cell As Range, Optional num As Integer)
Dim i&, id As String, st As String
If InStr(1, cell, "ID") = 0 Then
    TimID = "Khong tim thay"
    Exit Function
End If
st = Replace(Replace(Mid(cell, InStr(1, cell, "ID") + 2, 255), " ", ""), ".", "")
For i = 1 To Len(st) - num + 1
    id = Mid(st, i, num)
    If IsNumeric(id) Then
        TimID = id
        Exit Function
    End If
Next
End Function
View attachment 287969
Nhấp chuột vào đây để mở rộng...
Khi post câu hỏi thì em chỉ mong được chén món bình dân, không ngờ lại được chén sơn hào hải vị, he he!

Bác @bebo021999 chỉ nốt cho em nếu trường hợp chữ ID không phải là chữ hoa (mà là chữ thường) thì phải thêm gì vào dòng code kia không ạ?
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

huuthang_bd
Add-in DeleteVirusMacro - Xóa và ngăn chặn virus macro
    • Thích
    • Yêu thích
    • Chấp nhận
2 3
Trả lời
45
Đọc
14K
ngocthu90
ngocthu90
Uyên dương Hoya
Lỗi định dạng font chữ trong excel
Trả lời
3
Đọc
98
ptm0412
ptm0412
mucongnghenhat
lỗi đoạn văn bản bị đóng khung
Trả lời
1
Đọc
56
vietduc147258
V
T
  • Question Question
Xin code VBA trả về tên tài khoản theo điều kiện
    • Chấp nhận
Trả lời
12
Đọc
422
VetMini
V
HeSanbi
  • Question Question
VBA Nâng cao: Hướng dẫn tạo biểu thức tìm kiếm với Regular Expression
    • Thích
    • Yêu thích
Trả lời
8
Đọc
633
nguyendang95
N
ntnguyentam1907
Lấy bảng dữ liệu chứng khoán về excel
2
Trả lời
22
Đọc
3K
nguyendang95
N
PhanTuHuong
CHỌN VÀ SỬ DỤNG MÁY LỌC KHÔNG KHÍ
    • Cảm ơn
Trả lời
5
Đọc
763
hoangtuan.net
hoangtuan.net
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
    • Cảm ơn
14 15 16
Trả lời
305
Đọc
22K
pkt.bacdau
pkt.bacdau
HONGVY91
Đếm số lượng 2 cột và đếm không trùng
Trả lời
2
Đọc
125
hoangtuaotrang_hp_vn
H
hanhlx88
Nhờ giúp file Excel báo lỗi: Excel cannot complete this task with available resources
Trả lời
3
Đọc
187
hanhlx88
hanhlx88
C
Trách nhiệm pháp lý của chi nhánh doanh nghiệp Việt Nam tại nước ngoài với Công ty tại Việt Nam
Trả lời
3
Đọc
115
VetMini
V
HeSanbi
  • Đã giải quyết
LocalizeXL v1.73 - Làm nổi bật ô đang chọn và cuộn trang tự động
    • Thích
    • Yêu thích
2 3 4
Trả lời
73
Đọc
10K
HeSanbi
HeSanbi
L
Folder chứa chương trình bị khóa bởi Lock folder
2
Trả lời
28
Đọc
25K
qthangvphaui
Q
P
Xin trợ giúp cách lập lịch trực tịnh tiến dần cho 1 nhóm người.
Trả lời
4
Đọc
146
phambahoang
P
N
Tính tổng với điều kiện gần đúng
Trả lời
3
Đọc
71
HUONGHCKT
H
Bùi Thúy Thúy
  • Question Question
Lấy dữ liệu thời tiết Accuweather để điền vào nhật ký thi công
2 3 4
Trả lời
63
Đọc
19K
Mutants Men
M
Anxseo
  • Đã khóa
Cách crack my excel
Trả lời
1
Đọc
192
HeSanbi
HeSanbi
phatnguyen7301
  • Question Question
Tạo âm thanh khi nhấn vào một phím
Trả lời
5
Đọc
248
yaaasoo01
Y
anhtuan1066
  • Question Question
Chèn hình vào cell bằng hàm tự tạo
    • Thích
    • Ngạc nhiên
10 11 12
Trả lời
235
Đọc
106K
XaNguyen
X
vc_đi chơi
Khóa Hoặc mã hóa File excel có độ bảo mật cao
    • Cảm ơn
    • Thích
Trả lời
3
Đọc
215
Hoangkhang01213
Hoangkhang01213
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
Chúc mừng năm mới.
Back
Top Bottom