Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ hướng dẫn bạn cách tìm và trích xuất một số từ bên trong chuỗi văn bản trong Excel và Google Trang tính.
Tìm và trích xuất số từ chuỗi
Đôi khi dữ liệu của bạn có thể chứa số và văn bản và bạn muốn trích xuất dữ liệu số.
Nếu phần số nằm ở bên phải hoặc bên trái của chuỗi, thì việc tách số và văn bản tương đối dễ dàng. Tuy nhiên, nếu các số nằm bên trong chuỗi, tức là giữa hai chuỗi văn bản, bạn sẽ cần sử dụng một công thức phức tạp hơn nhiều (được hiển thị bên dưới).
TEXTJOIN - Trích xuất số trong Excel 2016+
Trong Excel 2016, hàm TEXTJOIN đã được giới thiệu, hàm này có thể trích xuất các số từ bất kỳ đâu của chuỗi văn bản. Đây là công thức:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) * 1), "")) |
Hãy xem công thức này hoạt động như thế nào.
Các Hàm ROW, INDIRECT và LEN trả về một mảng số tương ứng với mỗi ký tự trong chuỗi chữ và số của bạn. Trong trường hợp của chúng tôi, “Monday01Day” có 11 ký tự, do đó, hàm ROW sau đó sẽ trả về mảng {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1) * 1), "")) |
Tiếp theo, Hàm MID trích xuất từng ký tự từ chuỗi văn bản, tạo một mảng chứa chuỗi ban đầu của bạn:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "} * 1)," ")) |
Nhân mỗi giá trị trong mảng với 1 sẽ tạo ra một mảng chứa lỗi Nếu ký tự mảng là văn bản:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({# VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; 0; 1; #VALUE!; # VALUE!; # VALUE !}), "")) |
Tiếp theo, Hàm IFERROR, loại bỏ các giá trị lỗi:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Chỉ để lại Hàm TEXTJOIN kết hợp các số còn lại với nhau.
Lưu ý rằng công thức sẽ cung cấp cho bạn tất cả các ký tự số cùng nhau từ chuỗi của bạn. Ví dụ: nếu chuỗi chữ và số của bạn là Monday01Day01, kết quả là nó sẽ cung cấp cho bạn 0101.
Trích xuất số - Trước Excel 2016
Trước Excel 2016, bạn có thể sử dụng một phương pháp phức tạp hơn nhiều để trích xuất số từ văn bản. Bạn có thể sử dụng hàm FIND cùng với hàm IFERROR và MIN để xác định cả vị trí đầu tiên của phần số và vị trí đầu tiên của phần văn bản sau số. Sau đó, chúng tôi chỉ cần trích xuất phần số bằng hàm MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a") , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)))), 999999999)) - MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Lưu ý: Đây là công thức Mảng, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER, thay vì chỉ ENTER.
Hãy xem từng bước công thức này hoạt động như thế nào.
Tìm số nắm tay
Chúng ta có thể sử dụng hàm FIND để xác định vị trí bắt đầu của số.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Đối với đối số find_text của hàm FIND, chúng tôi sử dụng hằng số mảng {0,1,2,3,4,5,6,7,8,9}, làm cho hàm FIND thực hiện tìm kiếm riêng biệt cho từng giá trị trong hằng số mảng.
Đối số inside_text của hàm FIND trong trường hợp của chúng ta là Monday01Day, trong đó 1 có thể được tìm thấy ở vị trí 8 và 0 ở vị trí 7, vì vậy mảng kết quả của chúng tôi sẽ là: {8, # VALUE, # VALUE, # VALUE, # GIÁ TRỊ, # GIÁ TRỊ, # GIÁ TRỊ, #VALUE, # GIÁ TRỊ, 7}.
Sử dụng hàm IFERROR, chúng tôi thay thế các lỗi #VALUE bằng 999999999. Sau đó, chúng tôi chỉ cần tìm số tối thiểu trong mảng này và do đó lấy vị trí của số đầu tiên (7).
Xin lưu ý rằng công thức trên là công thức mảng, bạn phải nhấn Ctrl + Shift + Enter để kích hoạt nó.
Tìm ký tự văn bản đầu tiên sau số
Tương tự để định vị số đầu tiên trong chuỗi, chúng ta sử dụng hàm FIND để xác định vị trí văn bản bắt đầu lại sau khi số sử dụng tốt đối số start_num của hàm.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Công thức này hoạt động rất giống với công thức trước đó được sử dụng để định vị số đầu tiên, chỉ có điều chúng tôi sử dụng các chữ cái trong hằng số mảng và do đó các số gây ra lỗi #VALUE. Xin lưu ý rằng chúng tôi chỉ bắt đầu tìm kiếm sau vị trí được xác định cho số đầu tiên (đây sẽ là đối số start_num) chứ không phải từ đầu chuỗi.
Đừng quên nhấn Ctrl + Shift + Enter để sử dụng công thức trên.
Không có gì còn lại hơn là đặt tất cả những điều này lại với nhau.
Trích xuất số từ hai văn bản
Khi chúng ta đã có vị trí bắt đầu của phần số và vị trí bắt đầu của phần văn bản sau đó, chúng ta chỉ cần sử dụng hàm MID để trích xuất phần số mong muốn.
| 1 | = MID (B3, C3, D3-C3) |
Phương pháp khác
Không cần giải thích chi tiết hơn, bạn cũng có thể sử dụng công thức phức tạp dưới đây để lấy số từ bên trong một chuỗi.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (- MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)) * ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3))))) + 1,1) * 10 ROW (INDIRECT ("1:" & LEN (B3))) / 10) |
Xin lưu ý rằng công thức trên sẽ cung cấp cho bạn 0 khi không có số nào được tìm thấy trong chuỗi và các số 0 ở đầu sẽ bị bỏ trống.
Tìm và trích xuất số từ chuỗi thành văn bản trong Google Trang tính
Các ví dụ được hiển thị ở trên hoạt động theo cách tương tự trong Google Trang tính cũng như trong Excel.
