Excel lấy số từ chuỗi
|
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 Bản gốcVăn bảnSốTáo30Táo30quả đào24quả đào24quả cam12quả cam12quả đào0quả đào0Như đã 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 Khi bạn có vị trí, để chỉ trích xuất văn bản, hãy sử dụng Và, để chỉ trích xuất số, hãy sử dụng 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à 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 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 hướng dẫn này, chúng ta sẽ xem xét ba trường hợp mà bạn có thể có một danh sách văn bản hỗn hợp, từ đó bạn có thể muốn trích xuất các số
Chúng tôi sẽ xem xét ba công thức khác nhau có thể được sử dụng để trích xuất các số trong từng trường hợp Ở phần cuối của hướng dẫn, chúng ta cũng sẽ xem xét một số mã VBA mà bạn có thể sử dụng để thực hiện điều tương tự Chuẩn bị tinh thần, điều này có thể hơi phức tạp Mục lục
Xem xét ví dụ sau  Tại đây, mỗi ô có sự kết hợp giữa văn bản và số, với số luôn xuất hiện ở cuối văn bản. Trong những trường hợp như vậy, chúng ta sẽ cần sử dụng kết hợp các hàm Excel lồng nhau để trích xuất các số Các chức năng chúng ta sẽ sử dụng là
Về cơ bản, chúng ta sẽ sử dụng hoàn toàn các chức năng trên để thực hiện chuỗi tác vụ sau
Công thức mà chúng ta sẽ sử dụng để trích xuất các số từ ô A2 như sau =SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1),"")Hãy để chúng tôi phá vỡ công thức này để hiểu nó tốt hơn. Chúng ta sẽ đi từ các chức năng bên trong đến các chức năng bên ngoài
Hàm này cố gắng tìm vị trí của tất cả các số (0-9) trong ô A2. Do đó, nó trả về một công thức mảng {#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,9,7,8,#VALUE. } Nó trả về #VALUE. lỗi cho tất cả các chữ số trừ chữ số thứ 7, 8 và 9 vì không thể tìm thấy các số này (0,1,2,3,4,5,9) trong ô A2. Nó chỉ đơn giản là trả về vị trí của các số 6,7 và 8 lần lượt là các ký tự thứ 9, 7 và 8 trong A2
Tiếp theo, hàm IFERROR thay thế tất cả các phần tử lỗi của mảng bằng một khoảng trống (“”). Như vậy nó trả về công thức mảng {“”,””,””,””,””,””,9,7,8,””}
Sau đó, hàm MIN tìm phần tử mảng có giá trị nhỏ nhất. Về cơ bản, đây là vị trí của ký tự số đầu tiên trong A2. Lúc này hàm trả về giá trị 7
Tại thời điểm này, chúng tôi muốn trích xuất tất cả các ký tự văn bản từ A2 (để chúng tôi có thể xóa chúng). Vì vậy, chúng tôi muốn hàm LEFT trích xuất tất cả các ký tự bắt đầu ngược từ 7-1= ký tự thứ 6. Vì vậy, chúng tôi sử dụng công thức trên. Kết quả chúng tôi nhận được tại thời điểm này là “arnold”. Lưu ý rằng chúng ta đã trừ 1 từ tham số thứ hai của hàm LEFT
Bây giờ tất cả những gì còn lại phải làm là loại bỏ chuỗi thu được bằng cách thay thế nó bằng một khoảng trống. Điều này có thể dễ dàng đạt được bằng cách sử dụng hàm SUBSTITUTE. Cuối cùng, chúng tôi nhận được các ký tự số trong văn bản hỗn hợp, đó là “786” Khi bạn nhập xong công thức, hãy nhớ nhấn CTRL+SHIFT+Enter, thay vì chỉ nhấn phím quay lại. Điều này là do công thức liên quan đến mảng Tóm lại, đây là điều xảy ra khi bạn chia nhỏ công thức =SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),””))-1),”” =SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR({{#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,9,7,8,#VALUE. }),””))-1),””) =SUBSTITUTE(A2,LEFT(A2,MIN({“”,””,””,””,””,””,9,7,8,””}))-1),””) =SUBSTITUTE(A2,”arnold”,””) =786 Khi bạn kéo công thức xuống các ô còn lại, đây là kết quả bạn nhận được Bây giờ chúng ta hãy xem xét trường hợp các số luôn ở đầu Văn bản Xem xét ví dụ sau Tại đây, mỗi ô có sự kết hợp giữa văn bản và số, với số luôn xuất hiện ở đầu văn bản. Trong những trường hợp như vậy, một lần nữa chúng ta sẽ cần sử dụng kết hợp các hàm Excel lồng nhau để trích xuất các số Ngoài các hàm chúng ta đã sử dụng trong công thức trước, chúng ta sẽ sử dụng hai hàm bổ sung. đó là.
Về cơ bản, chúng tôi sẽ sử dụng các chức năng này hoàn toàn để thực hiện chuỗi tác vụ sau
Công thức mà chúng ta sẽ sử dụng để trích xuất các số từ ô A2 như sau =SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))),"") Hãy để chúng tôi phá vỡ công thức này để hiểu nó tốt hơn. Chúng ta sẽ đi từ các chức năng bên trong đến các chức năng bên ngoài
Hàm này cố gắng tìm vị trí của tất cả các số (0-9) trong ô A2. Do đó, nó trả về một công thức mảng {#GIÁ TRỊ. ,#GIÁ TRỊ. ,1,#VALUE. ,#GIÁ TRỊ. ,2,#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. } Nó trả về #VALUE. lỗi cho tất cả các chữ số trừ chữ số thứ 3 và thứ 6 vì không thể tìm thấy các số này (0,1,3,4,6,7,8,9) trong ô A2. Nó chỉ đơn giản trả về vị trí của số 2 và 5 tương ứng là ký tự thứ 1 và thứ 2 trong A2
Tiếp theo, hàm IFERROR thay thế tất cả các phần tử lỗi của mảng bằng một khoảng trống. Như vậy nó trả về công thức mảng {“”,””,1,””,””,2,””,””,””,””}
Sau đó, hàm MAX tìm phần tử mảng có giá trị cao nhất. Về cơ bản, đây là vị trí của ký tự số cuối cùng trong A2. Lúc này hàm trả về giá trị 2
Tại thời điểm này, chúng tôi muốn trích xuất tất cả các ký tự văn bản từ A2 (để chúng tôi có thể xóa chúng). Chúng tôi cần chỉ định có bao nhiêu ký tự chúng tôi muốn xóa. Điều này có được bằng cách tính độ dài của chuỗi trong A2 trừ đi vị trí của giá trị số cuối cùng. Như vậy ta sẽ được giá trị 8-2 = “6”
Bây giờ chúng ta có thể sử dụng hàm RIGHT để trích ra 6 ký tự bắt đầu từ ký tự thứ 2 trở đi. Kết quả chúng tôi nhận được tại thời điểm này là “arnold”
Bây giờ tất cả những gì còn lại phải làm là loại bỏ chuỗi này bằng cách thay thế nó bằng một khoảng trống. Điều này có thể dễ dàng đạt được bằng cách sử dụng hàm SUBSTITUTE. Cuối cùng, chúng tôi nhận được các ký tự số trong văn bản hỗn hợp, đó là “25” Một lần nữa, khi bạn nhập xong công thức, đừng quên nhấn CTRL+SHIFT+Enter, thay vì chỉ nhấn phím quay lại Tóm lại, đây là điều xảy ra khi bạn chia nhỏ công thức =SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A7),””))) =SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX(IFERROR({#VALUE. ,#GIÁ TRỊ. ,1,#VALUE. ,#GIÁ TRỊ. ,2,#VALUE. ,#GIÁ TRỊ. ,#GIÁ TRỊ. ,#GIÁ TRỊ. } ),””))),””) =SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-MAX({“”,””,1,””,””,2,””,””,””,””} )),””) =SUBSTITUTE(A7,RIGHT(A7,LEN(A7)-2),””) =SUBSTITUTE(A7,RIGHT(A7,8-2),””) =SUBSTITUTE(A7,”arnold”,””) =25 Khi bạn kéo công thức xuống các ô còn lại, đây là kết quả bạn nhận được Cuối cùng, chúng ta hãy xem xét trường hợp các số có thể ở bất kỳ đâu trong văn bản, có thể là phần đầu, phần cuối hoặc phần giữa của văn bản Hãy cùng xem ví dụ sau Tại đây, mỗi ô có sự kết hợp giữa văn bản và số, trong đó số xuất hiện trong bất kỳ phần nào của văn bản. Trong những trường hợp như vậy, chúng ta sẽ cần sử dụng kết hợp các hàm Excel lồng nhau để trích xuất các số Dưới đây là các chức năng mà chúng ta sẽ sử dụng lần này.
Lưu ý rằng công thức được thảo luận trong phần này sẽ chỉ hoạt động trong Excel phiên bản 2016 trở đi vì nó sử dụng hàm TEXTJOIN mới được giới thiệu. Nếu bạn đang sử dụng phiên bản Excel cũ hơn, thì bạn có thể cân nhắc sử dụng phương pháp VBA (thảo luận trong phần tiếp theo) Trong phương pháp này, về cơ bản chúng ta sẽ sử dụng hoàn toàn các chức năng trên để thực hiện chuỗi tác vụ sau
Công thức mà chúng ta sẽ sử dụng để trích xuất các số từ ô A2 như sau =TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))Hãy để chúng tôi phá vỡ công thức này để hiểu nó tốt hơn. Chúng ta sẽ đi từ các chức năng bên trong đến các chức năng bên ngoài
Hàm này tìm độ dài của chuỗi trong ô A2. Trong trường hợp của chúng tôi, nó trả về 13
Hàm này chỉ trả về một tham chiếu đến tất cả các hàng từ hàng 1 đến hàng 12
Bây giờ hàm này trả về số hàng của mỗi hàng này. Nói cách khác, nó chỉ trả về một mảng các số từ 1 đến 12. Do đó, hàm này trả về mảng {1;2;3;4;5;6;7;8;9;10;11;12;13} Ghi chú. Chúng tôi sử dụng hàm ROW() thay vì chỉ mã hóa cứng một mảng các số từ 1 đến 12 vì chúng tôi muốn có thể tùy chỉnh công thức này tùy thuộc vào kích thước của chuỗi đang được xử lý. Điều này sẽ đảm bảo rằng hàm sẽ tự điều chỉnh khi sao chép sang ô khác. ·
Tiếp theo, hàm MID trích xuất ký tự từ A2 tương ứng với từng vị trí được chỉ định trong mảng. Nói cách khác, nó trả về một mảng chứa mỗi ký tự của văn bản trong A2 dưới dạng một phần tử riêng biệt, như sau {“a”;”r”;”n”;”o”;”l”;”d”;”1″;”4″;”3″;”b”;”l”;”u”;”
Sau đó, hàm IFERROR sẽ thay thế tất cả các phần tử của mảng không phải là số. Điều này là do nó kiểm tra xem phần tử mảng có thể được nhân với 1 không. Nếu phần tử là một số, nó có thể dễ dàng được nhân lên để trả về cùng một số. Nhưng nếu phần tử không phải là ký tự số thì không thể nhân nó với 1 và do đó trả về lỗi. Hàm IFERROR ở đây chỉ định rằng nếu một phần tử có lỗi khi nhân, kết quả trả về sẽ trống. Như vậy nó trả về công thức mảng {“”;””;””;””;””;””;1;4;3;””;””;””;””}
Cuối cùng, chúng ta có thể kết hợp các phần tử mảng lại với nhau một cách đơn giản bằng cách sử dụng hàm TEXTJOIN. Hàm TEXTJOIN ở đây kết hợp các ký tự chuỗi còn lại (chỉ là các số) và bỏ qua các ký tự chuỗi trống. Cuối cùng, chúng tôi nhận được các ký tự số trong văn bản hỗn hợp, đó là “143” Khi bạn nhập xong công thức, đừng quên nhấn CTRL+SHIFT+Enter, thay vì chỉ nhấn phím quay lại Tóm lại, đây là điều xảy ra khi bạn chia nhỏ công thức =TEXTJOIN(“”,TRUE,IFERROR(MID(A2,ROW(INDIRECT(“1. ”&LEN(A2))),1)*1,””)) =TEXTJOIN(“”,TRUE,IFERROR(MID(A2,ROW(INDIRECT(“1. ”&13)),1)*1,””)) =TEXTJOIN(“”,TRUE,IFERROR(MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13},1)*1,””) =TEXTJOIN(“”,TRUE,IFERROR({“a”;”r”;”n”;”o”;”l”;”d”;”1″;”4″;”3″;”b” *1,””)) =TEXTJOIN(“”,TRUE,IFERROR(MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13},1)*1,””) =TEXTJOIN(“”,TRUE, {“”;””;””;””;””;””;1;4;3;””;””;””;””}) =143 Khi bạn kéo công thức xuống các ô còn lại, đây là kết quả bạn nhận được Phương pháp trên hoạt động đủ tốt trong việc trích xuất các số từ bất kỳ đâu trong văn bản hỗn hợp Tuy nhiên, nó yêu cầu một người sử dụng chức năng TEXTJOIN, chức năng này không khả dụng trong các phiên bản Excel cũ hơn (các phiên bản trước Excel 2016) Nếu bạn đang sử dụng phiên bản Excel không hỗ trợ TEXTJOIN, thì thay vào đó, bạn có thể sử dụng một đoạn mã VBA để hoàn thành công việc Nếu bạn chưa bao giờ sử dụng VBA trước đây, đừng lo lắng. Tất cả những gì bạn cần làm là sao chép mã bên dưới vào cửa sổ nhà phát triển VBA của bạn và chạy nó trên dữ liệu trang tính của bạn Đây là mã mà bạn sẽ cần phải sao chép ________số 8Đoạn mã trên tạo một hàm do người dùng định nghĩa có tên là ExtractNumbers() mà bạn có thể sử dụng trong trang tính của mình để trích xuất các số từ văn bản hỗn hợp trong bất kỳ ô nào Để đến cửa sổ nhà phát triển VBA của bạn, hãy làm theo các bước sau
Nhập hoặc sao chép-dán các dòng mã trên vào cửa sổ mô-đun. Mã của bạn hiện đã sẵn sàng để chạy Bây giờ, bất cứ khi nào bạn muốn trích xuất các số từ một ô, chỉ cần nhập tên của hàm, chuyển tham chiếu ô dưới dạng tham số. Vì vậy, để trích xuất các số từ một ô A2, bạn chỉ cần nhập hàm như sau vào một ô =ExtractNumbers(A2) Giải thích về Bộ luật Bây giờ chúng ta hãy dành chút thời gian để hiểu mã này hoạt động như thế nào
Hàm ExtractNumbers(CellRef As String)
Độ dài chuỗi mờ dưới dạng số nguyên StringLength = Len(CellRef)
Đối với i = 1 đến StringLength Nếu (IsNumeric(Mid(CellRef, i, 1))) Thì Kết quả = Kết quả & Giữa(CellRef, i, 1) tiếp theo tôi
ExtractNumbers = Kết quả Lưu ý rằng vì sổ làm việc hiện có mã VBA trong đó, bạn cần lưu nó với. xls hoặc. phần mở rộng xlsm Bạn cũng có thể chọn lưu mã này vào Sổ làm việc Macro cá nhân của mình, nếu bạn nghĩ rằng mình sẽ cần chạy mã này nhiều. Điều này sẽ cho phép bạn chạy mã trên bất kỳ sổ làm việc Excel nào của bạn Vâng, đó là rất nhiều Trong hướng dẫn này, chúng tôi đã chỉ cho bạn cách trích xuất số từ văn bản hỗn hợp trong excel Chúng tôi đã thấy ba trường hợp các con số nằm ở các phần khác nhau của văn bản. Chúng tôi cũng đã chỉ cho bạn cách sử dụng VBA để hoàn thành công việc một cách nhanh chóng |
