Tìm số trong văn bản Excel

=SUM(MID(0&B3, LỚN(INDEX(ISNUMBER(0+MID(B3,INDEX(ROW(INDIRECT(“1. ”&LEN(B3))),,),1))*(INDEX( ROW(INDIRECT(“1. ”&LEN(B3))),,)),0), INDEX(ROW(INDIRECT(“1. ”&LEN(B3))),,))+1,1) * 10^(INDEX(ROW(INDIRECT(“1. ”&LEN(B3))),,))/10)

Như với hầu hết các công thức tách hoặc trích xuất văn bản, điều quan trọng là xác định vị trí của thứ bạn đang tìm kiếm. Khi bạn có vị trí, bạn có thể sử dụng các chức năng khác để trích xuất những gì bạn cần

Trong trường hợp này, chúng tôi giả định rằng số và văn bản được kết hợp và số xuất hiện sau văn bản. Từ văn bản gốc xuất hiện trong một ô, bạn muốn tách văn bản và số thành các ô riêng biệt, như thế này

OriginalTextNumberApples30Apples30peaches24peaches24oranges12oranges12peaches0peaches0

Như đã nêu ở trên, chìa khóa trong trường hợp này là xác định vị trí bắt đầu của số, bạn có thể thực hiện điều này với công thức như sau

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

Khi bạn có vị trí, để chỉ trích xuất văn bản, hãy sử dụng

=LEFT(A1,position-1)

Và, để chỉ trích xuất số, hãy sử dụng

=RIGHT(A1,LEN(A1)-position+1)

Trong công thức đầu tiên ở trên, chúng ta đang sử dụng hàm FIND để xác định vị trí bắt đầu của số. Đối với find_text, chúng tôi đang sử dụng hằng số mảng {0,1,2,3,4,5,6,7,8,9}, điều này khiến hàm FIND thực hiện tìm kiếm riêng cho từng giá trị trong hằng số mảng. Vì hằng mảng chứa 10 số nên kết quả trả về sẽ là một mảng có 10 giá trị. Ví dụ: nếu văn bản gốc là "apples30" thì mảng kết quả sẽ là

{8,10,11,7,13,14,15,16,17,18}

Mỗi số trong mảng này đại diện cho vị trí của một mục trong hằng số mảng bên trong văn bản gốc

Tiếp theo, hàm MIN trả về giá trị nhỏ nhất trong danh sách, tương ứng với vị trí của số đầu tiên xuất hiện trong văn bản gốc. Về bản chất, hàm FIND nhận tất cả các vị trí số và MIN cho chúng ta vị trí số đầu tiên. lưu ý rằng 7 là giá trị nhỏ nhất trong mảng, tương ứng với vị trí của số 3 trong văn bản gốc

Bạn có thể thắc mắc về cấu trúc kỳ quặc của within_text trong hàm find

B5&"0123456789"

Phần này của công thức nối mọi số có thể có từ 0-9 với văn bản gốc ở B5. Thật không may, TÌM không trả về 0 khi không tìm thấy giá trị, vì vậy đây chỉ là một cách thông minh để tránh lỗi có thể xảy ra khi không tìm thấy số

Trong ví dụ này, vì chúng tôi giả định rằng số sẽ luôn xuất hiện ở vị trí thứ hai trong văn bản gốc, nên nó hoạt động tốt vì MIN chỉ buộc số nhỏ nhất hoặc lần xuất hiện đầu tiên của một số được trả về. Miễn là một số xuất hiện trong văn bản gốc, vị trí đó sẽ được trả lại

Nếu văn bản gốc không chứa bất kỳ số nào, vị trí "không có thật" bằng độ dài của văn bản gốc + 1 sẽ được trả về. Với vị trí không có thật này, công thức LEFT ở trên sẽ vẫn trả về văn bản và công thức RIGHT sẽ trả về một chuỗi rỗng ("")

Trong ví dụ này, mục tiêu là kiểm tra mật khẩu trong cột B để xem chúng có chứa số không. Đây là một vấn đề phức tạp đáng ngạc nhiên vì Excel không có chức năng cho phép bạn kiểm tra trực tiếp một số bên trong chuỗi văn bản. Lưu ý điều này khác với việc kiểm tra xem giá trị ô có phải là số không. Bạn có thể dễ dàng thực hiện kiểm tra đó với hàm ISNUMBER. Tuy nhiên, trong trường hợp này, chúng tôi sẽ kiểm tra xem một giá trị ô có chứa một số hay không, điều này có thể xảy ra ở bất kỳ đâu. Một giải pháp là sử dụng hàm FIND với hằng số mảng. Trong Excel 365, hỗ trợ công thức mảng động, bạn có thể sử dụng một công thức khác dựa trên hàm SEQUENCE. Cả hai cách tiếp cận được giải thích dưới đây

chức năng TÌM

Hàm TÌM được thiết kế để tìm một chuỗi con cụ thể bên trong một chuỗi văn bản. Nếu FIND tìm thấy chuỗi con, nó sẽ trả về vị trí của chuỗi con trong văn bản dưới dạng một số. Nếu không tìm thấy chuỗi con, FIND trả về lỗi #VALUE. Ví dụ

=FIND("p","apple") // returns 2
=FIND("z","apple") // returns #VALUE!

Chúng ta cũng có thể sử dụng ý tưởng này để kiểm tra các số

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

Thách thức trong trường hợp này là chúng ta cần kiểm tra các giá trị trong cột B cho mười số khác nhau, 0-9. Một cách để làm điều đó là cung cấp các số này dưới dạng hằng số mảng {0,1,2,3,4,5,6,7,8,9}. Đây là cách tiếp cận được thực hiện trong công thức trong ô D5

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))>0

Bên trong hàm COUNT, hàm TÌM được định cấu hình để tìm tất cả mười số trong ô B5

________số 8_______

Bởi vì chúng tôi đang đưa ra TÌM mười giá trị để tìm kiếm, nên nó trả về một mảng có 10 kết quả. Nói cách khác, TÌM kiểm tra văn bản trong B5 cho từng số và trả về tất cả kết quả cùng một lúc

{#VALUE!,4,5,6,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Trừ khi bạn nhìn vào mảng thường xuyên, điều này có thể trông khá khó hiểu. Đây là bản dịch. Số 1 được tìm thấy ở vị trí 4, số 2 được tìm thấy ở vị trí 5 và số 3 được tìm thấy ở vị trí 6. Không tìm thấy tất cả các số khác và trả về lỗi #VALUE.  

Bây giờ chúng ta đang ở rất gần với một công thức cuối cùng. Chúng ta chỉ cần kiểm đếm kết quả. Để làm điều này, chúng ta lồng công thức TÌM ở trên bên trong hàm COUNT như thế này

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))

FIND trả về mảng kết quả trực tiếp cho COUNT, đếm các số trong mảng. COUNT chỉ đếm các giá trị số và bỏ qua các lỗi. Điều này có nghĩa là COUNT sẽ trả về một số lớn hơn 0 nếu có bất kỳ số nào trong giá trị đang được kiểm tra. Trong trường hợp ô B5, COUNT trả về 3

Bước cuối cùng là kiểm tra kết quả từ COUNT và buộc kết quả TRUE hoặc FALSE. Chúng tôi làm điều này bằng cách thêm ">0" vào cuối công thức

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))>0

Bây giờ công thức sẽ trả về TRUE hoặc FALSE. Để hiển thị kết quả tùy chỉnh, bạn có thể sử dụng hàm IF

=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B5))>0, "Yes", "No")

Công thức ban đầu hiện được lồng bên trong IF làm đối số logic_test. Công thức này sẽ trả về "Có" nếu B5 chứa một số và "Không" nếu không

chức năng TRÌNH TỰ

Trong Excel 365, cung cấp các công thức mảng động, chúng ta có thể thực hiện một cách tiếp cận khác cho vấn đề này

=COUNT(--MID(B5,SEQUENCE(LEN(B5)),1))>0

Đây không nhất thiết là một cách tiếp cận tốt hơn, chỉ là một cách khác để giải quyết cùng một vấn đề. Về cốt lõi, công thức này sử dụng hàm MID cùng với hàm SEQUENCE để chia văn bản trong ô B5 thành một mảng

MID(B5,SEQUENCE(LEN(B5)),1)

Làm việc từ trong ra ngoài, hàm LEN trả về độ dài của văn bản trong ô B5

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

0

Số này được trả về hàm SEQUENCE làm đối số hàng và SEQUENCE trả về một mảng số, 1-6

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

1

Mảng này được trả về hàm MID dưới dạng đối số start_num và với num_chars được đặt thành 1, hàm MID trả về một mảng chứa các ký tự trong ô B5

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

2

Bây giờ chúng ta có thể đơn giản hóa công thức ban đầu thành

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

3

Chúng tôi sử dụng dấu phủ định kép (--) để Excel thử và ép các giá trị trong mảng thành số. Kết quả trông như thế này

=FIND(3,"app637") // returns 5
=FIND(9,"app637") // returns #VALUE!

4

Phép toán được tạo bởi dấu âm kép (--) trả về một số thực khi thành công và #VALUE. lỗi khi hoạt động không thành công. Hàm COUNT đếm số, bỏ qua mọi lỗi và trả về 3. Như trên chúng ta kiểm tra số đếm cuối cùng với ">0" và kết quả cho ô B5 là TRUE

Ghi chú. như bạn có thể đoán, bạn có thể dễ dàng điều chỉnh công thức này để đếm số trong một chuỗi văn bản

Ô bằng số?

Lưu ý rằng các công thức trên quá phức tạp nếu bạn chỉ muốn kiểm tra xem một ô có bằng một số hay không. Trong trường hợp đó, bạn chỉ cần sử dụng hàm ISNUMBER như thế này