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ách văn bản và số khỏi một chuỗi chữ và số trong Excel và Google Trang tính.
Tách số và văn bản khỏi chuỗi
Bài viết này sẽ thảo luận về cách tách số và văn bản nếu bạn có dữ liệu chữ và số trong đó phần đầu tiên là văn bản và phần cuối cùng là số (hoặc ngược lại). Bạn chỉ cần phần số từ đó. Đối với các trường hợp phức tạp hơn, hãy xem bài viết Cách Loại bỏ Ký tự Không phải Số.
Trích số từ bên phải
Trường hợp dễ nhất để trích xuất số từ một chuỗi là khi số có thể được tìm thấy ở cuối bên phải của chuỗi đó. Đầu tiên chúng ta xác định vị trí bắt đầu của một số bằng hàm FIND và sau đó trích xuất nó bằng hàm RIGHT.
| 1 | = RIGHT (B3, LEN (B3) -MIN (TÌM ({0,1,2,3,4,5,6,7,8,9}, B3 & "0123456789")) + 1) |
Hãy xem qua công thức trên.
Tìm số đầu tiên
Chúng ta có thể sử dụng hàm FIND để xác định vị trí bắt đầu của số.
| 1 | = MIN (TÌM ({0,1,2,3,4,5,6,7,8,9}, B3 & "0123456789")) |
Đố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 các tìm kiếm riêng biệt cho từng giá trị trong mảng hằng số.
Đối số trong_text của hàm FIND là giá trị ô & “0123456789”. Trong ví dụ của chúng tôi, “Thứ Hai010123456789”.
Vì hằng số mảng chứa 10 số nên kết quả sẽ là một mảng gồm 10 giá trị. Trong ví dụ của chúng tôi: {7,8,11,12,13,14,15,16,17,18}. Sau đó, chúng ta chỉ cần tìm số vị trí tối thiểu trong mảng này và do đó lấy vị trí của số đầu tiên.
Trích xuất phần số
Khi chúng ta có vị trí bắt đầu của số được tìm thấy ở cuối chuỗi chữ và số, chúng ta có thể sử dụng hàm RIGHT để trích xuất nó.
| 1 | = RIGHT (B3, LEN (B3) -C3 + 1) |
Trích xuất phần văn bản
Với vị trí bắt đầu của phần số chúng ta có thể đồng thời xác định được vị trí kết thúc của phần văn bản. Chúng ta có thể sử dụng hàm LEFT để giải nén nó.
| 1 | = LEFT (B3, C3-1) |
Một trường hợp phức tạp hơn để trích xuất các số từ một chuỗi là khi số có thể được tìm thấy ở đầu (tức là phía bên trái) của chuỗi. Rõ ràng, bạn không cần phải tìm vị trí bắt đầu của nó, thay vào đó là vị trí mà nó kết thúc. Đầu tiên, chúng tôi tìm vị trí của số cuối cùng với sự trợ giúp của hàm SUBSTITUTE và sau đó trích xuất số bằng hàm LEFT.
| 1 | = LEFT (B3, SUM (LEN (B3) -LEN (SUBSTITUTE (B3, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, "")))) |
Hãy xem qua công thức trên.
Tìm số cuối cùng
Với hàm SUBSTITUTE, bạn có thể thay thế từng số một bằng một chuỗi rỗng và sau đó tính tổng số lần bạn phải làm như vậy.
| 1 | = SUM (LEN (B3) -LEN (SUBSTITUTE (B3, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))) |
Khi bạn thay thế từng số một bằng một chuỗi rỗng, bạn sẽ nhận được mỗi khi một chuỗi có độ dài nhỏ hơn độ dài ban đầu một lần. Trong trường hợp của chúng tôi, độ dài của 1-Thứ Hai và 0-Thứ Hai đều bằng 8. Trừ độ dài này với độ dài ban đầu (trong trường hợp của chúng tôi là 9), bạn luôn nhận được 1. Khi bạn cộng các số này, bạn sẽ có được vị trí của số cuối cùng của mình .
Trích xuất phần số
Khi chúng ta có vị trí cuối cùng của số được tìm thấy ở đầu chuỗi chữ và số, chúng ta có thể sử dụng hàm LEFT để trích xuất nó.
| 1 | = LEFT (B3, C3) |
Trích xuất phần văn bản
Có vị trí cuối cùng của số được tìm thấy ở đầu chuỗi chữ và số của chúng ta, chúng ta đã có vị trí bắt đầu của phần văn bản và có thể sử dụng hàm RIGHT để trích xuất nó.
| 1 | = RIGHT (B3, LEN (B3) -C3) |
Văn bản và số riêng biệt trong Google Trang tính
Tất cả các ví dụ được giải thích ở trên hoạt động tương tự trong Google trang tính cũng như trong Excel.
