Note: The other languages of the website are Google-translated. Back to English
Đăng nhập  \/ 
x
or
x
Đăng ký  \/ 
x

or

Hướng dẫn Excel - trích xuất văn bản hoặc số theo vị trí cụ thể

Trong nhiều trường hợp, bạn có thể chỉ cần trích xuất nội dung hữu ích từ một câu hoặc chuỗi văn bản trong một ô, chẳng hạn như trích xuất tỉnh từ địa chỉ, trích xuất địa chỉ email từ một câu, trích xuất số hóa đơn chuyển phát nhanh từ một cuộc hội thoại, và như thế. Hướng dẫn này thu hẹp việc trích xuất đến một vị trí cụ thể trong một ô và thu thập các phương pháp khác nhau để giúp trích xuất văn bản hoặc số từ một ô theo vị trí cụ thể trong Excel.

Hướng dẫn này chủ yếu bao gồm: (Nhấp vào bất kỳ tiêu đề nào trong mục lục bên dưới hoặc bên phải để điều hướng đến nội dung tương ứng.)


Trích xuất văn bản theo vị trí

Phần này tập hợp các vị trí phổ biến nơi văn bản có thể được trích xuất từ ​​một ô và cung cấp các phương pháp tương ứng để xử lý chúng theo từng bước. Bạn có thể duyệt để biết thêm chi tiết.

1. Trích xuất số lượng ký tự từ trái hoặc phải

Để trích xuất số ký tự từ bên trái hoặc bên phải của một chuỗi, bạn có thể thử một trong các phương pháp dưới đây.

1.1 Trích xuất N ký tự đầu tiên hoặc cuối cùng bằng công thức

Giả sử bạn có danh sách chuỗi văn bản trong cột B như được hiển thị trong ảnh chụp màn hình bên dưới, để trích xuất 2 ký tự đầu tiên và 2 ký tự cuối cùng từ mỗi chuỗi, bạn có thể áp dụng các công thức sau.

Trích xuất N ký tự đầu tiên từ một chuỗi văn bản

Hàm LEFT có thể giúp dễ dàng trích xuất N ký tự đầu tiên từ một chuỗi văn bản trong Excel.

Công thức chung

=LEFT(text_string,[num_chars])

Lập luận

Text_string: Chuỗi văn bản có chứa các ký tự bạn muốn trích xuất. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Num_chars: Chỉ định số ký tự bạn muốn trích xuất.
Num_chars phải lớn hơn hoặc bằng XNUMX;
Nếu Num_chars được chỉ định lớn hơn độ dài của chuỗi văn bản, nó sẽ trả về toàn bộ văn bản;
Nếu Num_chars bị bỏ qua, nó được giả định là 1.

Bây giờ bạn có thể áp dụng công thức này để trích xuất 2 ký tự đầu tiên từ các ô trong cột B.

1. Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để nhận kết quả đầu tiên. Chọn ô kết quả và kéo Xử lý Tự động điền của nó xuống để áp dụng công thức cho các ô khác.

=LEFT(B5,2)

Bây giờ 2 ký tự đầu tiên trong mỗi ô của phạm vi B5: B10 đã được trích xuất.

Trích xuất N ký tự cuối cùng từ một chuỗi văn bản

Ở đây chúng tôi áp dụng hàm RIGHT để trích xuất N ký tự cuối cùng từ một chuỗi văn bản trong Excel.

Công thức chung

=RIGHT(text_string,[num_chars])

Lập luận

Text_string: Chuỗi văn bản có chứa các ký tự bạn muốn trích xuất. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Num_chars: Chỉ định số ký tự bạn muốn trích xuất.
Mô hình Num_chars phải lớn hơn hoặc bằng không;
Nếu được chỉ định Num_chars lớn hơn độ dài của chuỗi văn bản, nó trả về toàn bộ văn bản;
Nếu Num_chars bị bỏ qua, nó được giả định là 1.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Sau đó chọn ô kết quả này và kéo Tay cầm tự động điền của nó xuống để nhận các kết quả khác.

=RIGHT(B5,2)

1.2 Trích xuất N ký tự đầu tiên hoặc cuối cùng bằng một công cụ tuyệt vời

Mặc dù các công thức trên rất đơn giản, nhưng để trích xuất n ký tự đầu tiên hoặc cuối cùng từ một danh sách dài chuỗi văn bản, bạn vẫn cần phải kéo AutoFill Handle từ trên xuống dưới, việc này có thể hơi mất thời gian. Đây đề xuất Kutools cho Excel'S Trích xuất văn bản tiện ích giúp trích xuất hàng loạt N ký tự đầu tiên hoặc cuối cùng từ danh sách chuỗi văn bản.

1. Chọn trước danh sách chuỗi văn bản bạn muốn trích xuất văn bản và nhấp vào Kutools > bản văn > Trích xuất văn bản.

2. Trong cửa sổ bật lên Trích xuất văn bản hộp thoại, bạn cần cấu hình như sau.

2.1) Đảm bảo bạn đang ở trong Trích xuất theo vị trí chuyển hướng;
2.2) Trong Phạm vi , phạm vi đã chọn được hiển thị bên trong, bạn có thể thay đổi phạm vi khác theo nhu cầu của bạn;
2.3) Trong Các lựa chọn phần:
Nếu bạn muốn trích xuất N ký tự đầu tiên, hãy chọn N ký tự đầu tiên nút radio, và sau đó chỉ định số ký tự bạn sẽ trích xuất trong hộp văn bản. Trong trường hợp này, tôi nhập số 2;
Nếu bạn muốn trích xuất N ký tự cuối cùng, hãy chọn N ký tự cuối cùng nút radio, và sau đó chỉ định số ký tự bạn sẽ trích xuất trong hộp văn bản. Trong trường hợp này, tôi muốn trích xuất 2 ký tự cuối cùng từ chuỗi văn bản, vì vậy tôi nhập số 2 vào hộp văn bản.
2.4) Nhấp OK. Xem ảnh chụp màn hình:

Chú thích: Để làm cho kết quả động khi chuỗi văn bản thay đổi, bạn có thể kiểm tra Chèn dưới dạng công thức cái hộp.

3. Trong cửa sổ bật lên tiếp theo Trích xuất văn bản hộp thoại, chọn một ô để xuất các ký tự đã trích xuất và sau đó bấm OK.

Sau đó, N ký tự đầu tiên hoặc cuối cùng được chỉ định được trích xuất hàng loạt từ các ô đã chọn.

Bấm để biết thêm về tính năng này.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.


2. Trích xuất văn bản trước hoặc sau một ký tự / từ nhất định

Để trích xuất văn bản trước hoặc sau một ký tự hoặc từ nhất định, các kịch bản khác nhau trong phần này sẽ đáp ứng nhu cầu của bạn.

2.1 Trích xuất văn bản trước hoặc sau dấu phân cách đầu tiên (một ký tự)

Như được hiển thị trong ảnh chụp màn hình bên dưới, để trích xuất văn bản trước hoặc sau dấu phân cách đầu tiên từ mỗi ô trong phạm vi B4: B10, bạn có thể áp dụng một trong các phương pháp dưới đây.

2.1.1 Trích xuất văn bản trước dấu phân cách đầu tiên với công thức

Áp dụng công thức dựa trên các hàm LEFT và FIND có thể giúp trích xuất văn bản trước dấu phân cách đầu tiên từ một ô. Bạn có thể làm theo các bước dưới đây để hoàn thành.

Công thức chung

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất chuỗi con. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Dấu phân cách: Dấu phân cách đầu tiên xác định văn bản nào sẽ được trích xuất từ ​​một ô.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để nhận kết quả đầu tiên. Chọn ô kết quả đầu tiên và kéo Xử lý Tự động điền của nó xuống để nhận văn bản trước dấu phân cách đầu tiên của các ô khác.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Trích xuất văn bản sau dấu phân cách đầu tiên với công thức

Công thức dưới đây giúp trích xuất văn bản sau dấu phân cách đầu tiên từ một ô trong Excel.

Công thức chung

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất chuỗi con. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Dấu phân cách: Dấu phân cách đầu tiên xác định văn bản nào sẽ được trích xuất từ ​​một ô.
Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để nhận kết quả đầu tiên. Chọn ô kết quả đầu tiên và kéo Ô tự động điền của nó xuống để nhận các kết quả khác.

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Trích xuất văn bản trước hoặc sau dấu phân cách đầu tiên bằng một công cụ tuyệt vời

Ở đây rất khuyến khích Trích xuất văn bản tiện ích của Kutools cho Excel. Với tính năng này, bạn có thể dễ dàng trích xuất văn bản trước hoặc sau dấu phân cách đầu tiên từ một loạt ô với số lượng lớn.

1. Chọn phạm vi ô mà bạn muốn trích xuất văn bản, sau đó nhấp vào Kutools > bản văn > Trích xuất văn bản.

2. bên trong Trích xuất văn bản hộp thoại, bạn cần cấu hình như sau.

2.1) Ở trong Trích xuất theo vị trí chuyển hướng;
2.2) Trong Phạm vi , phạm vi đã chọn được hiển thị bên trong, bạn có thể thay đổi khi cần;
2.3) Trong Các lựa chọn phần:
Để trích xuất văn bản trước dấu phân cách đầu tiên, hãy chọn trước văn bản nút radio, rồi nhập dấu phân cách đầu tiên vào hộp văn bản;
Để trích xuất văn bản sau dấu phân cách đầu tiên, hãy chọn sau văn bản nút radio, rồi nhập dấu phân cách đầu tiên vào hộp văn bản.
2.4) Nhấp OK.

Lưu ý: Để làm cho kết quả động khi chuỗi văn bản thay đổi, bạn có thể chọn hộp Chèn dưới dạng công thức.

3. Sau đó khác Trích xuất văn bản hộp thoại bật lên, chọn một ô để xuất kết quả và nhấp vào OK.

Sau đó, các văn bản trước hoặc sau dấu phân cách đầu tiên được trích xuất từ ​​các ô đã chọn cùng một lúc.

Để biết thêm về tính năng này, vui lòng truy cập: Nhanh chóng trích xuất một số văn bản từ các ô trong Excel.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.

2.2 Trích xuất văn bản trước hoặc sau dấu phân cách cuối cùng (một ký tự)

Trong các bước trên, chúng ta đã học các phương pháp trích xuất văn bản trước hoặc sau dấu phân cách đầu tiên từ một ô. Như được hiển thị trong ảnh chụp màn hình bên dưới, phần này sẽ hiển thị cho bạn hai công thức để trích xuất văn bản trước hoặc sau dấu phân cách cuối cùng từ một ô. Bạn có thể làm theo các bước dưới đây để hoàn thành.

2.2.1 Trích xuất văn bản trước dấu phân cách cuối cùng với công thức

Để trích xuất văn bản trước dấu phân cách cuối cùng từ một ô, bạn có thể sử dụng các hàm SEARCH, LEN và SUBSTITUTE bên trong hàm LEFT.

Công thức chung

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất chuỗi con. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Dấu phân cách: Dấu phân cách cuối cùng xác định văn bản nào sẽ được trích xuất từ ​​một ô.

Chọn một ô, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này và kéo Xử lý Tự động điền của nó xuống để trích xuất văn bản từ các chuỗi văn bản khác trong cùng một cột.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Trích xuất văn bản sau dấu phân cách cuối cùng với công thức

Sau khi trích xuất văn bản trước dấu phân cách cuối cùng từ một ô, bạn có thể áp dụng công thức bên dưới để trích xuất văn bản sau dấu phân cách cuối cùng nếu bạn cần.

Công thức chung

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất chuỗi con. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
Dấu phân cách: Dấu phân cách cuối cùng xác định văn bản nào sẽ được trích xuất từ ​​một ô.

Chọn một ô, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này và kéo Xử lý Tự động điền của nó xuống để trích xuất văn bản từ các chuỗi văn bản khác trong cùng một cột.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Trích xuất văn bản sau ký tự thứ n

Nhìn vào ví dụ bên dưới, có một danh sách chuỗi văn bản trong phạm vi B4: B10, để trích xuất văn bản sau ký tự thứ ba từ mỗi ô, bạn có thể áp dụng công thức dựa trên hàm MID và hàm LEN.

Công thức chung

=MID(text_string,nth_char+1,LEN(text_string))

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất chuỗi con. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
nth_char: Một số đại diện cho ký tự thứ n và bạn sẽ trích xuất văn bản sau nó.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này và kéo Xử lý Tự động điền của nó xuống để nhận các kết quả khác.

=MID(B5,3+1,LEN(B5))

2.4 Trích xuất từ ​​thứ n từ một chuỗi văn bản

Giả sử bạn có một danh sách chuỗi văn bản như được hiển thị trong ảnh chụp màn hình bên dưới và chỉ muốn trích xuất từ ​​thứ n từ chuỗi văn bản, phần này cung cấp ba phương pháp để bạn thực hiện.

2.4.1 Trích xuất từ ​​thứ n với công thức

Bạn có thể kết hợp các hàm TRIM, MID, SUBSTITUTE, REPT và LEN để trích xuất từ ​​thứ n từ một chuỗi văn bản trong một ô.

Công thức chung

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất từ ​​thứ n. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
N: Một số đại diện cho từ thứ n mà bạn sẽ trích xuất từ ​​chuỗi văn bản.

Trong trường hợp này, phạm vi B5: B10 chứa các chuỗi văn bản, D5: D10 chứa các số đại diện cho từ thứ n, hãy áp dụng công thức này để trích xuất từ ​​thứ n từ chuỗi văn bản.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để nhận kết quả đầu tiên. Chọn ô kết quả này và kéo Xử lý Tự động điền của nó xuống để lấy từ thứ n của các ô khác.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Lưu ý: Bạn có thể nhập trực tiếp số thứ n vào công thức như sau.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Trích xuất từ ​​thứ n với chức năng Do người dùng định nghĩa

Ngoài công thức trên, bạn cũng có thể áp dụng một hàm do người dùng định nghĩa để trích xuất từ ​​thứ n từ một ô trong Excel.

1. Nhấn nút Khác + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. bên trong Microsoft Visual Basic cho các ứng dụng cửa sổ, nhấp Chèn > mô-đun, và sau đó sao chép VBA bên dưới vào cửa sổ Mã.

Mã VBA: Trích xuất từ ​​thứ n từ một chuỗi văn bản trong một ô

Hàm ExtractTheNthWord (Nguồn dưới dạng chuỗi, Vị trí là Số nguyên) 'Cập nhật bởi Extendoffice 20211202 Dim arr () As String arr = VBA.Split (Nguồn, "") xCount = UBound (arr) Nếu xCount <1 Hoặc (Vị trí - 1)> xCount Hoặc Vị trí <0 Thì FindWord = "" Khác FindWord = arr ( Vị trí - 1) Kết thúc Nếu Kết thúc Chức năng

3. Nhấn nút Khác + Q chìa khóa để đóng Microsoft Visual Basic cho các ứng dụng cửa sổ.

4. Quay lại trang tính có chứa các chuỗi văn bản mà bạn muốn trích xuất từ ​​thứ n. Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để lấy từ thứ n.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Lưu ý: trong công thức, D5 là ô chứa một số đại diện cho từ thứ n. Ngoài ra, bạn có thể thay thế trực tiếp tham chiếu ô bằng một số.

5. Chọn ô kết quả và kéo Tay cầm Tự động điền của nó xuống để trích xuất từ ​​thứ n từ chuỗi văn bản của các ô khác.

2.4.3 Trích xuất từ ​​thứ n bằng một công cụ tuyệt vời

Nếu bạn không muốn áp dụng theo cách thủ công một công thức hoặc một hàm do người dùng xác định được cung cấp ở trên, thì đây đề xuất Kutools cho Excel's Trích xuất từ ​​thứ n trong ô tính thiết thực. Với tính năng này, bạn có thể dễ dàng trích xuất từ ​​thứ n từ một chuỗi văn bản trong một ô chỉ với một vài cú nhấp chuột.

1. Chọn một ô để đặt kết quả và nhấp vào Kutools > Công thức trợ giúp > bản văn > Trích xuất từ thứ n trong ô. Xem ảnh chụp màn hình:

2. bên trong Trình trợ giúp công thức hộp thoại, bạn cần cấu hình như sau.

2.1) Trong Chọn một danh sách công thức cái hộp, cái Trích xuất từ ​​thứ n trong ô tùy chọn được đánh dấu;
2.2) Trong Pin , hãy chọn một ô chứa chuỗi văn bản mà bạn muốn trích xuất từ ​​thứ n;
2.3) Trong Thứ N ô, chọn ô chứa số thứ n hoặc nhập trực tiếp một số tùy theo nhu cầu của bạn;
2.4) Nhấp OK.

3. Sau đó, từ thứ n (thứ hai) được trích xuất từ ​​chuỗi văn bản trong ô B5 và bạn có thể thấy một công thức được tạo cùng một lúc. Chọn ô kết quả này và kéo Xử lý Tự động điền của nó xuống để lấy từ thứ n từ các chuỗi văn bản khác.

Bấm để biết thêm về tính năng này.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.

2.5 Trích xuất văn bản trước hoặc sau lần xuất hiện thứ n của dấu phân cách

Giả sử bạn có một danh sách các chuỗi văn bản như được hiển thị trong ảnh chụp màn hình bên dưới. Để trích xuất văn bản trước hoặc sau lần xuất hiện thứ hai của khoảng trắng, phần này cung cấp hai công thức để giúp bạn thực hiện.

2.5.1 Trích xuất văn bản trước lần xuất hiện thứ n của dấu phân cách

Bạn có thể sử dụng hàm LEFT cùng với các hàm SUBSTITUTE và FIND để trích xuất văn bản trước lần xuất hiện thứ n của dấu phân cách từ một ô trong Excel.

Công thức chung

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất văn bản. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
N: Một số đại diện cho lần xuất hiện thứ n của dấu phân cách mà bạn sẽ trích xuất văn bản trước nó.

Chọn một ô, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này và kéo ô Tự động điền Xử lý xuống để nhận các kết quả khác trong danh sách.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Lưu ý: Trong công thức, B5 là ô chứa chuỗi văn bản mà bạn muốn trích xuất văn bản; “” Ở đây đại diện cho một khoảng trắng và số 2 đại diện cho lần xuất hiện thứ hai của một khoảng trắng. Bạn có thể thay đổi chúng theo nhu cầu của bạn.

2.5.2 Trích xuất văn bản sau lần xuất hiện thứ n của dấu phân cách

Để trích xuất văn bản sau lần xuất hiện thứ n của dấu phân cách, bạn có thể áp dụng hàm RIGHT với các hàm SUBSTITUTE, LEN và FIND.

Công thức chung

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Lập luận

Text_string: Chuỗi văn bản mà bạn muốn trích xuất văn bản. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;
N: Một số đại diện cho lần xuất hiện thứ n của dấu phân cách mà bạn sẽ trích xuất văn bản sau nó.

Bây giờ bạn có thể áp dụng công thức này để trích xuất văn bản sau lần xuất hiện thứ hai của khoảng trắng từ mỗi ô trong phạm vi B5: B10 như sau.

Chọn một ô, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này và kéo ô Tự động điền Xử lý xuống để nhận các kết quả khác.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Trích xuất văn bản trước hoặc sau ngắt dòng

Giả sử bạn có một danh sách đơn hàng trong cột B và bạn chỉ muốn trích xuất phần ngày tháng và phần Số sản phẩm từ mỗi ô. Bạn có thể sử dụng các công thức Excel dưới đây để thực hiện.

2.6.1 Trích xuất văn bản trước ngắt dòng đầu tiên với công thức

Như bạn có thể thấy trong ảnh chụp màn hình ở trên, phần ngày nằm trước dấu ngắt dòng đầu tiên trong ô. Phần này sẽ trình bày hàm TRÁI cùng với hàm TÌM KIẾM để giúp bạn trích xuất văn bản trước khi ngắt dòng đầu tiên trong một ô.

Công thức chung

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Lập luận

Di Động: Ô mà bạn muốn trích xuất văn bản trước dấu ngắt dòng đầu tiên.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới vào đó và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Sau đó, bạn có thể thấy văn bản trước khi ngắt dòng đầu tiên trong mỗi ô trong phạm vi B5: B8 được trích xuất như trong ảnh chụp màn hình bên dưới.

Lưu ý: Trong công thức, CHAR (10) biểu thị ngắt dòng trên Windows.

2.6.2 Trích xuất văn bản sau dấu ngắt dòng cuối cùng với công thức

Trong bước trước, chúng ta đã nói về cách trích xuất văn bản trước khi ngắt dòng đầu tiên trong một ô. Và phần này sẽ hướng dẫn bạn cách trích xuất văn bản sau khi ngắt dòng cuối cùng trong một ô với một công thức khác.

Công thức chung

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Lập luận

Di Động: Ô mà bạn muốn trích xuất văn bản trước dấu ngắt dòng đầu tiên.

Chọn một ô trống, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức cho các ô khác.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Sau đó, sản phẩm không. một phần của mỗi ô trong danh sách được trích xuất như trong ảnh chụp màn hình ở trên.

Lưu ý: Trong công thức, CHAR (10) biểu thị ngắt dòng trên Windows.

2.7 Trích xuất văn bản trước hoặc sau một từ

Trong các phần trước, chúng ta đã học cách trích xuất văn bản trước hoặc sau một ký tự hoặc dấu phân cách. Bạn nên làm gì để trích xuất văn bản trước hoặc sau toàn bộ một từ? Phần này sẽ giới thiệu ba phương pháp giúp bạn hoàn thành nhiệm vụ này.

2.7.1 Trích xuất văn bản trước một từ nhất định với công thức

Công thức sau đây giúp bạn trích xuất văn bản trước một từ nhất định trong một ô trong Excel.

Công thức chung

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Lập luận

Di Động: Ô mà bạn muốn trích xuất văn bản trước một từ nhất định.
từ: Từ bạn muốn trích xuất tất cả văn bản trước nó. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;

Chọn một ô trống, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

Trong ví dụ này, chúng ta sẽ trích xuất tất cả các văn bản trước từ “Excel”, vì vậy chúng ta nhập trực tiếp từ này vào công thức và đặt nó trong dấu ngoặc kép. Hoặc bạn có thể tham chiếu đến ô có chứa từ “Excel”.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Ghi chú:

1) Công thức này phân biệt chữ hoa chữ thường.
2) Nếu từ bạn đã nhập trùng lặp trong cùng một ô, công thức chỉ trích xuất văn bản sau lần xuất hiện đầu tiên.

2.7.2 Trích xuất văn bản sau một từ nhất định với công thức

Để trích xuất văn bản sau một từ nhất định, bạn có thể áp dụng công thức sau để thực hiện.

Công thức chung

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Lập luận

Di Động: Ô mà bạn muốn trích xuất văn bản sau một từ nhất định.
từ: Từ bạn muốn trích xuất tất cả văn bản sau nó. Nó có thể là một tham chiếu ô hoặc một chuỗi văn bản thực được đặt trong dấu ngoặc kép;

Chọn một ô, nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Sau đó, bạn có thể thấy tất cả các văn bản sau khi từ “Excel” trong mỗi ô được trích xuất như thể hiện trong ảnh chụp màn hình bên dưới.

Ghi chú:

1) Công thức này không phân biệt chữ hoa chữ thường.
2) Nếu từ bạn đã nhập trùng lặp trong cùng một ô, công thức chỉ trích xuất văn bản sau lần xuất hiện đầu tiên.

2.7.3 Trích xuất văn bản trước hoặc sau một từ nhất định bằng một công cụ tuyệt vời

Nếu bạn cảm thấy rằng việc sử dụng sữa công thức có thể gây ra nhiều bất tiện, thì đây thực sự khuyến nghị Trích xuất văn bản tiện ích của Kutools cho Excel. Tính năng này giúp tự động hóa tác vụ Trích xuất trong Excel chỉ với một vài cú nhấp chuột.

1. nhấp chuột Kutools > bản văn > Trích xuất văn bản để kích hoạt tính năng này.

2. bên trong Trích xuất văn bản hộp thoại, hãy thực hiện các cài đặt sau.

2.1) Đảm bảo rằng bạn đang ở trong Trích xuất theo vị trí chuyển hướng;
2.2) Trong Phạm vi hộp, nhấp vào để chọn phạm vi ô mà bạn muốn trích xuất văn bản;
2.3) Trong Các lựa chọn phần:
Để trích xuất tất cả các văn bản trước một từ, hãy chọn trước văn bản nút radio và sau đó nhập từ vào hộp văn bản;
Để trích xuất tất cả các văn bản sau một từ, hãy chọn sau văn bản nút radio và sau đó nhập từ vào hộp văn bản.
2.4) Nhấp vào OK cái nút. Xem ảnh chụp màn hình:

Ghi chú: Nếu bạn muốn tạo kết quả động, hãy kiểm tra Chèn dưới dạng công thức hộp. Sau đó, kết quả sẽ tự động cập nhật khi dữ liệu trong phạm vi thay đổi.

3. Sau đó an Trích xuất văn bản hộp thoại bật lên, bạn cần chọn một ô để xuất kết quả và sau đó nhấp vào OK .

Sau đó, các văn bản trước hoặc sau một từ nhất định trong mỗi ô trong phạm vi đã chọn sẽ được trích xuất ngay lập tức.

Lưu ý: Tính năng này phân biệt chữ hoa chữ thường.

Bấm để biết thêm về tính năng này.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.


3. Trích xuất giữa các ký tự / từ

Nếu bạn muốn trích xuất văn bản giữa các ký tự hoặc từ nhất định, hãy thử các phương pháp sau.

3.1 Trích xuất văn bản giữa hai ký tự

Để trích xuất văn bản giữa hai ký tự, có thể là các ký tự giống nhau hoặc khác nhau. Phần này cung cấp một số phương pháp và bạn có thể chọn một trong số chúng tùy theo nhu cầu của mình.

3.1.1 Trích xuất văn bản giữa hai ký tự giống nhau bằng công thức

Như được hiển thị trong ảnh chụp màn hình bên dưới, có một danh sách các chuỗi văn bản trong cột B và bạn muốn trích xuất phần số giữa các ký tự “/” từ mỗi ô trong phạm vi, công thức sau có thể giúp bạn.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả, sau đó kéo ô Tự động điền Xử lý xuống để nhận kết quả của các ô khác trong danh sách.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Sau đó, văn bản giữa hai ký tự giống nhau “/” được trích xuất từ ​​mỗi ô trong phạm vi. Xem ảnh chụp màn hình:

Lưu ý:

1) B5 là ô mà bạn muốn trích xuất văn bản giữa hai ký tự giống nhau từ đó;
2) "/”Là hai ký tự giống nhau mà bạn muốn trích xuất văn bản giữa chúng.
Bạn cần thay đổi các biến này dựa trên dữ liệu của riêng bạn.

3.1.2 Trích xuất văn bản giữa hai ký tự khác nhau bằng công thức

Sau khi tìm hiểu cách trích xuất văn bản giữa hai ký tự giống nhau trong một ô, ở đây chúng tôi sẽ trình bày công thức để trích xuất văn bản giữa hai ký tự khác nhau. Như được hiển thị trong ảnh chụp màn hình bên dưới, để chỉ trích xuất địa chỉ email giữa “<” và “>” từ mỗi ô trong cột B, bạn có thể làm như sau.

Công thức chung

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Lập luận

Di Động: Ô mà bạn muốn trích xuất văn bản giữa hai ký tự khác nhau;
End_char: Kí tự cuối của hai nhân vật khác nhau;
Start_char: Ký tự bắt đầu của hai ký tự khác nhau.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Bạn có thể thấy rằng chỉ văn bản giữa các ký tự được chỉ định được trích xuất như trong ảnh chụp màn hình ở trên.

3.1.3 Trích xuất văn bản giữa hai ký tự bằng một công cụ tuyệt vời

Ở đây rất khuyến khích Trích xuất chuỗi giữa văn bản được chỉ định tính năng của Kutools cho Excel để giúp bạn dễ dàng trích xuất văn bản giữa hai ký tự giống nhau hoặc khác nhau trong một ô trong Excel.

1. Chọn một ô trống để xuất kết quả, sau đó nhấp vào Kutools > Công thức trợ giúp > Công thức trợ giúp.

2. bên trong Trình trợ giúp công thức hộp thoại, hãy thực hiện các cài đặt sau.

2.1) Kiểm tra Lọc và sau đó nhập từ "Trích xuất" vào hộp văn bản;
2.2) Trong Chọn một danh sách công thức hộp, nhấp vào Trích xuất chuỗi giữa văn bản được chỉ định Tùy chọn;
2.3) Trong Đầu vào đối số phần:
Trong tạp chí Pin , chọn một ô mà bạn muốn trích xuất văn bản (ở đây tôi chọn ô B5);
Trong tạp chí Bắt đầu (các) ký tự , nhập ký tự bắt đầu của hai ký tự khác nhau;
Trong tạp chí Kết thúc (các) hộp ký tự, nhập ký tự kết thúc của hai ký tự khác nhau.
2.4) Nhấp Được. Xem ảnh chụp màn hình:

3. Sau đó, chỉ văn bản giữa “<” và “>” trong ô B5 được trích xuất. Trong khi đó, một công thức đã được tạo, bạn có thể chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để trích xuất văn bản từ các ô khác trong cùng danh sách.

Bấm để biết thêm về tính năng này.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.

3.1.4 Trích xuất văn bản giữa hai ký tự (bao gồm các ký tự) theo quy tắc

Nếu bạn muốn giữ lại hai ký tự sau khi giải nén, hãy thử áp dụng một quy tắc trong tính năng Trích xuất Văn bản của Kutools cho Excel.

1. nhấp chuột Kutools > bản văn > Trích xuất văn bản.

2. bên trong Trích xuất văn bản hộp thoại, hãy thực hiện các cài đặt sau.

2.1) Nhấp vào Trích xuất theo quy tắc chuyển hướng;
2.2) Trong Phạm vi phần, nhấp vào để chọn phạm vi ô mà bạn muốn trích xuất văn bản giữa các ký tự;
2.3) Trong bản văn hộp, nhập <*>;
Lời khuyên: "<"Và">”Là các ký tự bạn muốn trích xuất văn bản giữa chúng, * là ký tự đại diện đại diện cho bất kỳ số ký tự nào. Bạn có thể nhập điều kiện theo nhu cầu của bạn.
2.4) Nhấp vào Thêm để thêm điều kiện vào Mô tả quy tắc hộp danh sách;
2.5) Nhấp vào Ok cái nút. Xem ảnh chụp màn hình:

3. Khác Trích xuất văn bản hộp thoại bật lên, vui lòng chọn một ô để xuất kết quả, sau đó nhấp vào OK .

Sau đó, văn bản giữa các ký tự được chỉ định (bao gồm các ký tự) được trích xuất hàng loạt từ mỗi ô trong phạm vi đã chọn.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.

3.2 Trích xuất văn bản giữa hai từ

Ngoài việc trích xuất văn bản giữa hai ký tự, bạn cũng có thể cần trích xuất văn bản giữa hai từ. Ví dụ: trích xuất tất cả các chuỗi văn bản giữa hai từ “KTE” và “feature” từ mỗi ô trong cột B như được hiển thị trong ảnh chụp màn hình bên dưới. Bạn có thể thử một trong các phương pháp sau để thực hiện.

3.2.1 Trích xuất văn bản giữa hai từ bằng công thức

Bạn có thể sử dụng công thức dựa trên hàm MID và hàm TÌM KIẾM để trích xuất tất cả các chuỗi văn bản giữa hai từ trong một ô.

Công thức chung

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Lập luận

Di Động: ô mà bạn muốn trích xuất tất cả các chuỗi văn bản giữa hai từ;
Start_word: từ bắt đầu của hai từ mà bạn muốn trích xuất tất cả các chuỗi văn bản sau nó;
Chiều dài1: độ dài ký tự của từ bắt đầu.
End_word: từ cuối của hai từ mà bạn muốn trích xuất tất cả các chuỗi văn bản trước nó.
Chiều dài2: độ dài ký tự của từ bắt đầu cộng với 1.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Chú thích: Trong công thức, số 3 thể hiện độ dài ký tự của từ "KTE"; số 4 đại diện cho độ dài ký tự của từ "KTE" cộng với 1.

Bạn có thể thấy tất cả các chuỗi văn bản giữa hai từ được chỉ định được trích xuất từ ​​mỗi ô trong cột B.

3.2.2 Trích xuất văn bản giữa hai từ bằng một công cụ tuyệt vời

Đối với nhiều người dùng Excel, các công thức có thể khó nhớ và khó xử lý. Kèm theo Trích xuất chuỗi giữa văn bản được chỉ định tính năng của Kutools cho Excel, bạn có thể dễ dàng trích xuất văn bản giữa hai từ chỉ với một vài cú nhấp chuột.

1. Chọn một ô để xuất kết quả, sau đó nhấp vào Kutools > Công thức trợ giúp > Trình trợ giúp Công thức.

2. bên trong Công thức trợ giúp hộp thoại, bạn cần cấu hình như sau.

2.1) Kiểm tra Lọc và sau đó nhập từ "Trích xuất" vào hộp văn bản;
2.2) Trong Chọn một danh sách công thức hộp, nhấp vào Trích xuất chuỗi giữa văn bản được chỉ định Tùy chọn;
2.3) Trong Đầu vào đối số phần:
Trong tạp chí Pin , chọn một ô mà bạn muốn trích xuất văn bản (ở đây tôi chọn ô B5);
Trong tạp chí Bắt đầu (các) ký tự , nhập từ bắt đầu của hai từ bạn muốn trích xuất tất cả các chuỗi văn bản sau nó;
Trong tạp chí Kết thúc ký tự , hãy nhập từ kết thúc của hai từ bạn muốn trích xuất tất cả các chuỗi văn bản trước nó.
2.4) Nhấp Được. Xem ảnh chụp màn hình:

3. Sau đó, tất cả các chuỗi văn bản giữa hai từ “KTE” và “feature” trong ô B5 được trích xuất. Trong khi đó, một công thức đã được tạo, bạn có thể chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để trích xuất văn bản từ các ô khác trong cùng danh sách.

  Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.


Trích xuất các số theo vị trí

Đối với danh sách các chuỗi chữ và số, có thể có ba trường hợp:

  1. Số ở đầu văn bản;
  2. Số ở cuối văn bản;
  3. Số có thể ở bất kỳ đâu trong văn bản.

Trong phần này, chúng tôi sẽ cung cấp các phương pháp khác nhau có thể được sử dụng để trích xuất các con số trong từng trường hợp đã đề cập ở trên.

1 Trích xuất số từ bên trái của một chuỗi

Phần này sẽ giới thiệu một công thức để giúp bạn chỉ trích xuất các số xuất hiện trước văn bản trong một ô.

Công thức chung

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Lập luận

Di Động: Ô mà bạn muốn trích xuất số ở bên trái của chuỗi văn bản.

Lưu ý:

1) Nếu bạn đang sử dụng Excel 2019 trở về trước, bạn cần nhấn Ctrl + sự thay đổi + Đi vào phím để xác nhận công thức mảng này.
2) Nếu bạn đang sử dụng Excel 365 hoặc Excel 2021, chỉ cần xác nhận công thức này với Đi vào Chìa khóa.

Chọn một ô trống, nhập công thức bên dưới và nhấn Ctrl + sự thay đổi + Đi vào or Đi vào phím để lấy kết quả. Chọn ô kết quả này và sau đó kéo Xử lý Tự động điền của nó xuống để lấy số lượng các ô khác.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Ghi chú:

1) Nếu một ô chỉ chứa các số, thì toàn bộ số sẽ được trích xuất.
2) Công thức này chỉ trích xuất các số ở bên trái của một chuỗi văn bản. Nếu các số tồn tại ở giữa hoặc ở cuối chuỗi văn bản, chúng sẽ bị bỏ qua.

2 Trích xuất số từ bên phải của một chuỗi

Như được hiển thị trong ảnh chụp màn hình bên dưới, để chỉ trích xuất các số xuất hiện sau văn bản trong một ô, hãy thử công thức sau.

Công thức chung

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Lập luận

Di Động: Ô mà bạn muốn trích xuất số từ đầu chuỗi văn bản.

Lưu ý:

1) Nếu bạn đang sử dụng Excel 2019 trở về trước, bạn cần nhấn Ctrl + sự thay đổi + Đi vào phím để xác nhận công thức mảng này.
2) Nếu bạn đang sử dụng Excel 365 hoặc Excel 2021, chỉ cần xác nhận công thức này bằng phím Enter.

Chọn một ô trống, nhập công thức bên dưới và nhấn Ctrl + sự thay đổi + Đi vào or Đi vào phím để lấy kết quả. Chọn ô kết quả này và sau đó kéo Xử lý Tự động điền của nó xuống để lấy số lượng các ô khác.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Ghi chú:

1) Nếu một ô chỉ chứa các số, thì toàn bộ số sẽ được trích xuất.
2) Công thức này chỉ trích xuất các số từ bên phải của một chuỗi văn bản. Nếu các số tồn tại ở giữa hoặc đầu chuỗi văn bản, chúng sẽ bị bỏ qua.

3. Trích xuất tất cả các số từ bất kỳ vị trí nào trong chuỗi văn bản

Các phương pháp trên chỉ giúp trích xuất số từ bên trái hoặc bên phải của một chuỗi văn bản. Nếu bạn muốn trích xuất tất cả các số từ bất kỳ đâu trong chuỗi văn bản, thì ở đây chúng tôi cung cấp ba phương pháp để bạn thực hiện.

3.1 Trích xuất tất cả các số từ bất kỳ đâu trong một chuỗi với công thức

Bạn có thể áp dụng công thức sau để trích xuất tất cả các số từ bất kỳ đâu trong chuỗi văn bản trong Excel.

1. Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy tất cả các số từ ô B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Chọn ô kết quả rồi kéo Xử lý Tự động điền của nó xuống để lấy tất cả các ô khác.

3.2 Trích xuất tất cả các số từ bất kỳ đâu trong chuỗi bằng VBA

Công thức trên quá dài và phức tạp đối với nhiều người dùng Excel. Trên thực tế, bạn có thể chạy tập lệnh VBA để tự động hóa tác vụ trong Excel. Bạn có thể làm như sau.

1. Nhấn nút Khác + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Trong phần mở đầu Microsoft Visual Basic cho các ứng dụng cửa sổ, nhấp Chèn > Module. Sau đó sao chép VBA bên dưới vào cửa sổ Mã mô-đun.

Mã VBA: Trích xuất tất cả các số từ bất kỳ đâu trong chuỗi văn bản

Sub ExtrNumbersFromRange () 'được cập nhật bởi Extendoffice 20220106 Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox ("Vui lòng chọn chuỗi văn bản : ", xTitleId," ", Type: = 8) If TypeName (xDRg) =" Nothing "Then Exit Sub Set xRRg = Application.InputBox (" Vui lòng chọn ô đầu ra: ", xTitleId," ", Type: = 8) If TypeName (xRRg) = "Nothing" then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len (xRg) For xNumber = 1 To nCellLength If IsNumeric (Mid (xRg, xNumber, 1)) Sau đó strNumber = strNumber & Mid (xRg, xNumber, 1) End If Next xNumber xRRg.Item (xI) = strNumber strNumber = "" Next xRg End Sub

3. Nhấn nút F5 phím để chạy mã. Trong phần mở đầu KutoolsforExcel hộp thoại, chọn phạm vi ô mà bạn muốn trích xuất tất cả các số từ mỗi ô, sau đó bấm vào OK .

4. Sau đó khác KutoolsforExcel hộp thoại bật lên. Trong hộp thoại này, hãy chọn một ô đích và nhấp vào OK.

Sau đó, tất cả các số được trích xuất hàng loạt từ mỗi ô trong phạm vi đã chọn.


4. Trích xuất các số sau văn bản cụ thể

Như được hiển thị trong ảnh chụp màn hình bên dưới, để trích xuất bất kỳ số nào sau văn bản cụ thể “Không”, phần này cung cấp hai phương pháp để giúp bạn thực hiện.

4.1 Trích xuất các số sau một văn bản cụ thể với công thức

Bạn có thể áp dụng công thức sau để trích xuất các số sau một văn bản cụ thể trong một ô trong Excel.

Công thức chung:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

Lập luận

Di Động: Ô mà bạn muốn trích xuất các số sau một văn bản cụ thể;
Bản văn: Văn bản bạn muốn trích xuất số sau nó.

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn Đi vào phím để lấy kết quả. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Ghi chú:

1) Nếu một ô không chứa văn bản cụ thể, công thức sẽ trả về # N / A.
2) Công thức phân biệt chữ hoa chữ thường.

4.2 Trích xuất các số sau một văn bản cụ thể với chức năng do người dùng xác định

Hàm do người dùng xác định sau đây cũng có thể giúp trích xuất các số sau một văn bản cụ thể trong một ô. Hãy làm như sau.

1. Nhấn nút Khác + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. bên trong Microsoft Visual Basic cho các ứng dụng cửa sổ, nhấp Chèn > mô-đun, và sau đó sao chép mã VBA bên dưới vào cửa sổ Mã mô-đun.

Mã VBA: trích xuất các số sau một văn bản cụ thể trong một ô

Hàm GetNumberAfterTheChar (Rng As Range, Char As String) 'được cập nhật bởi Extendoffice 20220106 Dim xValue As String Dim xRntString As String Dim xStart As Integer Dim xC xValue = Rng.Text xStart = InStr (1, xValue, Char, vbTextCompare) If IsEmpty (xStart) then GetNumberAfterTheChar = "" Exit Function End If Sau đó GetNumberAfterTheChar = "" Thoát hàm Kết thúc Nếu xStart = xStart - 1 + Len (Char) Nếu xStart <1 Thì GetNumberAfterTheChar = "" Thoát hàm Kết thúc Nếu xValue = Mid (xValue, xStart + 1) xRntString = "" Cho xI = 1 To Len (xValue) xC = Mid (xValue, xI, 1) Select Case Asc (xC) Case 1 To 48 xRntString = xRntString & xC Case Else For End Chọn tiếp GetNumberAfterTheChar = xRntString End Function

3. Nhấn nút Khác + Q chìa khóa để đóng Microsoft Visual Basic cho các ứng dụng cửa sổ.

4. Chọn một ô, nhập công thức bên dưới và nhấn Đi vào Chìa khóa. Chọn ô kết quả này rồi kéo Xử lý Tự động điền của nó xuống để áp dụng công thức này cho các ô khác.

=GetNumberAfterTheChar(B5,"No. ")

Ghi chú:

1) Trong công thức này, B5 là ô mà bạn muốn trích xuất số và “Không. ”Là văn bản cụ thể mà bạn muốn trích xuất số sau nó. Bạn có thể thay đổi chúng khi bạn cần.
2) Nếu có dấu phân cách giữa văn bản cụ thể và số, vui lòng thêm dấu phân cách ở cuối văn bản. Trong trường hợp này, tôi đã thêm một khoảng trắng sau văn bản “Không”, cuối cùng được hiển thị là “Không. ”.
3) Phương pháp này không phân biệt chữ hoa chữ thường;
4) Nếu một ô không chứa văn bản cụ thể, công thức sẽ trả về kết quả trống.

Các bài liên quan:

Hướng dẫn Excel: Tách ô văn bản, số và ngày (Tách thành nhiều cột)
Hướng dẫn này được chia thành ba phần: chia ô văn bản, chia ô số và chia ô ngày tháng. Mỗi phần cung cấp các ví dụ khác nhau để giúp bạn biết cách xử lý công việc tách khi gặp cùng một vấn đề.
Bấm để biết thêm ...

Excel Thêm văn bản và số vào vị trí cụ thể của ô
Trong Excel, thêm văn bản hoặc số vào ô là một công việc rất phổ biến. Chẳng hạn như thêm khoảng cách giữa các tên, thêm tiền tố hoặc hậu tố vào ô, thêm dấu gạch ngang vào số xã hội. Ở đây trong hướng dẫn này, nó liệt kê hầu hết tất cả các trường hợp thêm trong Excel và cung cấp các phương pháp tương ứng cho bạn.
Bấm để biết thêm ...

Excel Xóa ký tự, từ, số khỏi chuỗi văn bản
Giả sử, bạn có một danh sách dài các chuỗi văn bản chứa các ký tự, số hoặc các ký hiệu cụ thể khác. Trong một số trường hợp nhất định, bạn có thể cần xóa một số ký tự dựa trên vị trí, chẳng hạn như từ phải, trái hoặc giữa khỏi chuỗi văn bản hoặc xóa một số ký tự không mong muốn, số khỏi danh sách chuỗi. Tìm từng giải pháp một sẽ khiến bạn đau đầu, hướng dẫn này thu thập tất cả các loại phương pháp xóa ký tự, từ hoặc số trong Excel.
Bấm để biết thêm ...


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Super Formula Bar (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.