Cách tách số và văn bản khỏi chuỗi trong Excel
Nhiều lần tôi nhận được dữ liệu hỗn hợp từ trường và máy chủ để phân tích. Dữ liệu này thường bẩn, có cột bị trộn lẫn với văn bản số ans. Trong khi làm sạch dữ liệu trước khi phân tích, tôi tách các số và văn bản trong các cột riêng biệt. Trong bài viết này, tôi sẽ cho bạn biết cách bạn có thể làm điều đó.
Tình huống:
Vì vậy, một người bạn của chúng tôi trên Exceltip.com đã hỏi câu hỏi này trong phần nhận xét. “Làm cách nào để tách các số đứng trước văn bản và ở cuối văn bản bằng Công thức excel. Ví dụ: 125EvenueStreet và LoveYou3000, v.v. ” Để trích xuất văn bản, chúng tôi sử dụng các hàm RIGHT, LEFT, MID và các hàm văn bản khác. Chúng ta chỉ cần biết số lượng văn bản cần trích xuất. Và ở đây chúng tôi sẽ làm tương tự trước.
Trích xuất Số và Văn bản từ một Chuỗi khi Số ở cuối Chuỗi. Ví dụ trên, tôi đã chuẩn bị trang tính này. Trong Ô A2, tôi có chuỗi. Trong ô B2, tôi muốn phần văn bản và trong C2 là Phần số.
Vì vậy, chúng ta chỉ cần biết vị trí mà số bắt đầu. Sau đó, chúng tôi sẽ sử dụng Left và chức năng khác. Vì vậy, để có được vị trí của số đầu tiên, chúng tôi sử dụng công thức chung dưới đây:
Công thức chung để lấy vị trí của số đầu tiên trong chuỗi:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")
Điều này sẽ trả về vị trí của số đầu tiên.
Ví dụ trên viết công thức này trong bất kỳ ô nào.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))
Trích phần văn bản
Nó sẽ trả về 15 vì số đầu tiên được tìm thấy ở vị trí thứ 15 trong Văn bản. Tôi sẽ giải thích nó sau.
Công thức trích xuất văn bản từ bên trái
=LEFT(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)
Trích Số Phần
Bây giờ để lấy số, chúng ta chỉ cần lấy các ký tự số từ số đầu tiên được tìm thấy. Vì vậy, chúng tôi tính tổng chiều dài của chuỗi và trừ vị trí của số đầu tiên tìm thấy và cộng 1 vào nó. Đơn giản. Vâng, nó chỉ nghe có vẻ phức tạp, nó đơn giản.
Công thức tách số từ bên phải
=RIGHT(A5,LEN(A5)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)
Vì vậy, phần hàm LEFT và RIGHT đơn giản. Phần Tricky là MIN và SEARCH Part cho chúng ta vị trí của số được tìm thấy đầu tiên. Hãy hiểu điều đó.
Cách thức hoạt động
Chúng tôi biết hàm LEFT và RIGHT hoạt động như thế nào. Chúng ta sẽ khám phá phần chính của công thức này có vị trí của số đầu tiên được tìm thấy và đó là:
SEARCH (‘text’, ‘string’) hàm nhận hai đối số, đầu tiên là văn bản bạn muốn tìm kiếm, thứ hai là chuỗi mà bạn muốn tìm kiếm.
Ở đây trong TÌM KIẾM, tại vị trí văn bản, chúng ta có một mảng các số từ 0 đến 9. Và tại vị trí chuỗi, chúng ta có chuỗi được nối với ” 0123456789 “sử dụng toán tử & *. Tại sao? Tôi sẽ cho bạn biết.
-
Mỗi phần tử trong mảng \ {0,1,2,3,4,5,6,7,8,9} sẽ được tìm kiếm trong chuỗi đã cho và sẽ trả về vị trí của nó trong chuỗi dạng mảng tại cùng một chỉ mục trong mảng.
-
Nếu không tìm thấy bất kỳ giá trị nào, nó sẽ gây ra lỗi. Do đó, tất cả công thức sẽ dẫn đến lỗi. Tới tránh điều này, chúng tôi đã nối các số “0123456789” trong văn bản. Để nó luôn tìm thấy từng số trong chuỗi.
Những số này cuối cùng là do đó sẽ không gây ra bất kỳ vấn đề.
-
Bây giờ Hàm MIN trả về giá trị nhỏ nhất từ mảng do hàm SEARCH trả về. Giá trị nhỏ nhất này sẽ là số đầu tiên trong chuỗi. Bây giờ bằng cách sử dụng hàm NUMBER và LEFT và RIGHT này, chúng ta có thể chia các phần văn bản và chuỗi.
Hãy xem xét ví dụ của chúng tôi. Trong A5 chúng ta có chuỗi có tên đường và số nhà. Chúng ta cần tách chúng trong các ô khác nhau.
Trước tiên, hãy xem cách chúng ta có được vị trí của số đầu tiên trong chuỗi.
MIN (SEARCH (\ {0,1,2,3,4,5,6,7,8,9}, A5 & “0123456789”)): điều này sẽ chuyển thành MIN (SEARCH (\ {0,1,2, 3,4,5,6,7,8,9}, ”Monta270123456789 *”))
Bây giờ, như tôi đã giải thích, tìm kiếm sẽ tìm kiếm từng số trong mảng \ {0,1,2,3,4,5,6,7,8,9} trong Monta270123456789 và sẽ trả về vị trí của nó ở dạng mảng. Mảng được trả về sẽ là \ {8,9,6,11,12,13,14,7,16,17}. Làm sao?
0 sẽ được tìm kiếm trong chuỗi. Nó được tìm thấy ở vị trí 8. Do đó phần tử đầu tiên của chúng ta là 8. Lưu ý rằng văn bản gốc của chúng ta chỉ dài 7 ký tự. Hiểu rồi. 0 không phải là một phần của Monta27. Tiếp theo 1 sẽ được tìm kiếm trong chuỗi và nó cũng không phải là một phần của chuỗi gốc và chúng tôi nhận được vị trí của nó là 9.
Tiếp theo 2 sẽ được tìm kiếm. Vì nó là một phần của chuỗi gốc, chúng tôi lấy chỉ mục của nó là 6.
Tương tự mỗi phần tử được tìm thấy ở một số vị trí.
Và câu chuyện sau này khá đơn giản. Chúng tôi sử dụng số này trích xuất văn bản và số bằng Hàm LEFT và RIGHT.
Trích xuất số và văn bản từ một chuỗi khi Số ở đầu chuỗi Trong ví dụ trên, Số ở cuối chuỗi. Làm thế nào để chúng tôi trích xuất số và văn bản khi số ở đầu.
Tôi đã chuẩn bị một bảng tương tự như trên. Nó chỉ có số trong đầu.
Ở đây chúng tôi sẽ sử dụng một kỹ thuật khác. Chúng tôi sẽ đếm độ dài của các số (ở đây là 2) và sẽ trích xuất số ký tự đó từ bên trái của Chuỗi.
Vì vậy, phương thức là = LEFT (chuỗi, đếm số)
Để Đếm số ký tự, đây là Công thức.
Công thức chung để đếm số lượng:
=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8","9"},""))
Bây giờ hàm LEN sẽ trả về độ dài của các ký tự trong một mảng của các chuỗi đó.
Sau đó, Từ độ dài của các chuỗi ban đầu, chúng ta sẽ trừ độ dài của mỗi chuỗi được trả về bởi hàm SUBSTITUTE. Điều này sẽ lại trả về một mảng.
Bây giờ SUM sẽ thêm tất cả các số này. Đây là số lượng các số trong chuỗi.
Trích xuất một phần số từ chuỗi
Bây giờ vì chúng ta biết độ dài của các số trong chuỗi, chúng ta sẽ thay thế hàm này bằng LEFT.
Vì chúng tôi có chuỗi A11 của chúng tôi:
Công thức trích số từ TRÁI
=LEFT(A11,SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Trích xuất một phần văn bản từ chuỗi
Vì chúng ta biết số lượng các số, chúng ta có thể trừ nó khỏi tổng chiều dài của chuỗi để có các bảng chữ cái số trong chuỗi và sau đó sử dụng hàm right để trích xuất số ký tự đó từ bên phải của chuỗi.
Công thức trích xuất văn bản từ RIGHT
=RIGHT(A11,LEN(A2)-SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Cách thức hoạt động
Phần chính trong cả hai công thức là SUM (LEN (A11) -LEN (SUBSTITUTE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}, “”)))
tính toán lần xuất hiện đầu tiên của một số. Chỉ sau khi tìm thấy điều này, chúng tôi có thể tách văn bản và số bằng hàm LEFT. Vì vậy, hãy hiểu điều này.
-
SUBSTITUTE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}, ” “):
Phần này trả về một mảng chuỗi trong A11 sau khi thay thế các số này bằng không / trống (“”). Đối với 27Monta, nó sẽ trả về \ {“27Monta”, “27Monta”, “7Monta”, “27Monta”, “27Monta”, “27Monta”, “27Monta”, “2Monta”, “27Monta”, “27Monta”}.
LEN (SUBSTITUTE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}, ” “)):
Bây giờ phần SUBSTITUTE được bao bọc bởi hàm LEN. Độ dài văn bản trong mảng trả về này được trả về bởi hàm SUBSTITUTE. Kết quả là chúng ta sẽ có \ {7,7,6,7,7,7,7,6,7,7}.
LEN (A11) -LEN (SUBSTITUTE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, ” 9 “},” “)):
Ở đây chúng tôi đang trừ từng số trả về ở phần trên với độ dài của chuỗi thực tế. Độ dài của văn bản gốc là 7. Do đó, chúng ta sẽ có \ {7-7,7-7,7-6, ….}. Cuối cùng chúng ta sẽ có \ {0,0,1.0,0,0,0,1,0,0}.
SUM (LEN (A11) -LEN (SUBSTITUTE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8” , “9”}, “”))):
Ở đây chúng tôi đã sử dụng SUM để tính tổng mảng được trả về bởi phần trên của hàm.
Điều này sẽ cho 2. Đó là số lượng các số trong chuỗi.
Bây giờ bằng cách sử dụng này, chúng tôi có thể trích xuất các văn bản và số và chia chúng trong các ô khác nhau. Phương thức này sẽ hoạt động với cả văn bản kiểu, khi số ở đầu và khi ở cuối. Bạn chỉ cần sử dụng Hàm LEFT và RIGHT một cách thích hợp.
Sử dụng chức năng SplitNumText để tách số và văn bản từ một chuỗi
Các phương thức ở trên cho hơi phức tạp và chúng không hữu ích khi văn bản và số được trộn lẫn. Để tách văn bản và số, hãy sử dụng chức năng do người dùng xác định này.
Cú pháp:
=SplitNumText(string, op)
Chuỗi: Chuỗi bạn muốn tách.
Op: đây là boolean. Vượt qua 0 hoặc false để lấy phần văn bản. Đối với phần số, chuyển true hoặc bất kỳ số nào lớn hơn 0.
Ví dụ, nếu chuỗi ở dạng A20 thì
Công thức trích xuất các số từ chuỗi là:
=SplitNumText(A20,1)
Và
Công thức để trích xuất văn bản từ chuỗi là:
=SplitNumText(A20,0)
Sao chép mã bên dưới trong mô-đun VBA để làm cho công thức trên hoạt động.
Function SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i, 1) Else txt = txt & Mid(str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function
Mã này chỉ đơn giản là kiểm tra từng ký tự trong chuỗi, nếu nó là một số hay không. Nếu nó là một số thì nó được lưu trữ trong biến num khác trong biến txt. Nếu người dùng chuyển true cho op thì num được trả về còn txt được trả về.
Đây là cách tốt nhất để tách số và văn bản từ một chuỗi theo ý kiến của tôi.
Bạn có thể tải xuống sổ làm việc tại đây nếu bạn muốn.
Vì vậy, các bạn, đây là những cách để tách văn bản và số trong các ô khác nhau. Hãy cho tôi biết nếu bạn có bất kỳ nghi ngờ nào hoặc bất kỳ giải pháp nào tốt hơn trong phần bình luận bên dưới. Nó luôn luôn thú vị để tương tác với các chàng trai.