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.

---

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

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 đăng ký hạng mục thi 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

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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.

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.

---

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

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 đăng ký hạng mục thi 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

=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.

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.

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

Chọn một ô, nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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

Chọn một ô, nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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

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 đăng ký hạng mục thi 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

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 đăng ký hạng mục thi 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 ô

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
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. 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 đăng ký hạng mục thi 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.

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.

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

Chọn một ô, sao chép hoặc nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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

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 đăng ký hạng mục thi 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

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 đăng ký hạng mục thi 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

Chọn một ô trống, nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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

Chọn một ô trống, nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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ú:

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

Chọn một ô, nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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ú:

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.

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.

---

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 đăng ký hạng mục thi 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 ý:

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

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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.

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.

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.

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.

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

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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.

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.

---

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

Lưu ý:

Chọn một ô trống, nhập công thức bên dưới và nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi or đăng ký hạng mục thi 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ú:

---

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

Lưu ý:

Chọn một ô trống, nhập công thức bên dưới và nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi or đăng ký hạng mục thi 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ú:

---

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 đăng ký hạng mục thi 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()
'Updated by 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("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", 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)) Then
        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

Chọn một ô trống, sao chép hoặc nhập công thức bên dưới và nhấn đăng ký hạng mục thi 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ú:

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 ô

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by 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
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   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 đăng ký hạng mục thi 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ú:

---

Các bài liên quan: