Hướng dẫn excel remove numbers from string formula - excel xóa số khỏi công thức chuỗi
|
Hướng dẫn này sẽ dạy bạn cách tách văn bản khỏi các số trong Excel bằng cách sử dụng các công thức gốc và các hàm tùy chỉnh. Bạn cũng sẽ học cách chia văn bản và số thành hai cột riêng biệt. Show Hãy tưởng tượng điều này: Bạn nhận được dữ liệu thô để phân tích và tìm ra rằng các số được trộn với văn bản trong một cột. Trong hầu hết các tình huống, chắc chắn sẽ thuận tiện hơn khi có chúng trong các cột riêng biệt để kiểm tra kỹ hơn. Trong trường hợp bạn đang làm việc với dữ liệu đồng nhất, có lẽ bạn có thể sử dụng các chức năng bên trái, phải và giữa để trích xuất cùng một số ký tự từ cùng một vị trí. Nhưng đó là một kịch bản lý tưởng cho các xét nghiệm trong phòng thí nghiệm. Trong cuộc sống thực, bạn rất có thể đối phó với dữ liệu không giống nhau khi các số đến trước văn bản, sau văn bản hoặc giữa văn bản. Các ví dụ dưới đây cung cấp giải pháp chính xác cho trường hợp này. Cách xóa văn bản và giữ số trong các ô ExcelGiải pháp hoạt động trong Excel 365, Excel 2021 và Excel 2019 Microsoft Excel 2019 đã giới thiệu một vài chức năng mới không có sẵn trong các phiên bản trước và chúng tôi sẽ sử dụng một trong những chức năng như vậy, cụ thể là TextJoin, để loại bỏ các ký tự văn bản từ một ô chứa các số. Công thức chung là: TextJoin ("", true, iferror (mid (ô, hàng (gián tiếp ("1:" & len (ô))), 1) *1, "")) Trong Excel 365 và 2021, cái này cũng sẽ hoạt động: TextJoin ("", true, iferror (mid (cell, sequence (len (ô)), 1) *1, ""))) Ngay từ cái nhìn đầu tiên, các công thức có thể trông hơi đáng sợ, nhưng chúng hoạt động :) Ví dụ: để xóa văn bản khỏi các số trong A2, nhập một trong các công thức dưới đây trong B2, sau đó sao chép nó xuống bao nhiêu ô khi cần. Trong Excel 365 - 2019:
Trong Excel 2019, nó phải được nhập dưới dạng công thức mảng với Ctrl + Shift + Enter. Trong mảng động Excel, nó hoạt động như một công thức bình thường được hoàn thành với phím Enter.Ctrl + Shift + Enter. In dynamic array Excel, it works as a normal formula completed with the Enter key. Trong Excel 365 và 2021:
Kết quả là, tất cả các ký tự văn bản bị xóa khỏi một ô và các số được giữ:  Công thức này hoạt động như thế nào: Để hiểu rõ hơn về logic, hãy bắt đầu điều tra công thức từ bên trong: Bạn sử dụng một hàng (gián tiếp ("1:" & len (chuỗi)))) hoặc trình tự (len (chuỗi)) để tạo chuỗi một số tương ứng với tổng số ký tự trong chuỗi nguồn, sau đó cung cấp các số tuần tự đó thành các số hàm giữa là số bắt đầu. Trong B2, phần này của công thức trông như sau:
Hàm MID trích xuất từng ký tự từ A2 bắt đầu với chính đầu tiên và trả về chúng dưới dạng một mảng:
Mảng này được nhân với 1. Các giá trị số tồn tại mà không thay đổi, trong khi nhân một ký tự không phải là số kết quả trong #Value! lỗi:
Hàm iferror xử lý các lỗi này và thay thế chúng bằng các chuỗi trống:
Mảng cuối cùng này được phục vụ cho hàm textjoin, kết nối các giá trị không trống trong mảng (bỏ qua đối số được đặt thành true) bằng cách sử dụng chuỗi trống ("") cho dấu phân cách:
Mẹo. Đối với Excel 2016 - 2007, một giải pháp cũng tồn tại, nhưng công thức phức tạp hơn nhiều. Bạn có thể tìm thấy nó trong hướng dẫn này: Cách trích xuất số trong Excel. For Excel 2016 - 2007, a solution also exists, but the formula is far more complex. You can find it in this tutorial: How to extract numbers in Excel. Chức năng tùy chỉnh để xóa văn bản khỏi các sốGiải pháp hoạt động cho tất cả các phiên bản Excel Nếu bạn đang sử dụng phiên bản Excel cũ hơn hoặc thấy các công thức ở trên quá khó nhớ, không có gì ngăn bạn tạo chức năng của riêng mình với cú pháp đơn giản hơn và tên thân thiện với người dùng như RemoveText. Chức năng do người dùng xác định (UDF) có thể được viết theo hai cách: Mã VBA 1: Ở đây, chúng tôi nhìn vào từng ký tự trong chuỗi nguồn từng một và kiểm tra xem nó có phải là số hay không. Nếu một số, ký tự được thêm vào chuỗi kết quả. Loại bỏ chức năng (asstring str) dim sres asstring sres = "" cho i = 1 đến len (str) iftrue = isnumeric (mid (str, i, 1)) sau đó sres = sres & amp; Mid (str, i, 1) endifnext i removeText = sres endfeft RemoveText(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If True = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveText = sRes End Function Mã VBA 2: Mã tạo ra một đối tượng để xử lý một biểu thức chính quy. Sử dụng regexp, chúng tôi xóa tất cả các ký tự khác ngoài chữ số 0-9 khỏi chuỗi nguồn. Hàm xóaText (str asstring) asstringWithCreateObject ("vbscript.regexp") .global = true .potyern = "[^0-9]" removeText =. RemoveText(str As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[^0-9]" RemoveText = .Replace(str, "") End With End Function Trên bảng tính nhỏ, cả hai mã sẽ hoạt động tốt như nhau. Trên các bảng tính lớn nơi chức năng được gọi là hàng trăm hoặc hàng ngàn lần, mã 2 sử dụng vbscript.regexp sẽ hoạt động nhanh hơn. Các bước chi tiết để chèn mã trong sổ làm việc của bạn có thể được tìm thấy ở đây: Cách chèn mã VBA vào Excel. Bất kỳ cách tiếp cận nào bạn chọn, từ quan điểm của người dùng cuối, chức năng xóa văn bản và để lại số đơn giản như thế này: RemoveText(string) Chẳng hạn, để loại bỏ các ký tự không phải là số từ ô A2, công thức trong B2 là:
Chỉ cần sao chép nó xuống cột và bạn sẽ nhận được kết quả này: Ghi chú. Cả hai công thức gốc và đầu ra chức năng tùy chỉnh là một chuỗi số. Để biến nó thành một số, nhân kết quả với 1 hoặc thêm 0 hoặc bọc công thức trong hàm giá trị. Ví dụ: Both the native formulas and custom function output a numeric string. To turn it into a number, multiply the result by 1, or add zero, or wrap the formula in the VALUE function. For example:
Cách xóa số khỏi chuỗi văn bản trong ExcelGiải pháp hoạt động trong Excel 365, Excel 2021 và Excel 2019 Các công thức để loại bỏ các số từ một chuỗi chữ và số khá giống với các công thức được thảo luận trong ví dụ trước. Cho Excel 365 - 2019: TextJoin ("", true, if (iserr (mid (cell, hàng (gián tiếp ("1:" & len (ô))), 1) *1), mid (ô, hàng (gián tiếp ("1:" & len ( ô))), 1), "")) Trong Excel 2019, hãy nhớ biến nó thành một công thức mảng bằng cách nhấn Ctrl + Shift + Enter Keys với nhau.array formula by pressing the Ctrl + Shift + Enter keys together. Cho Excel 365 và 2021: TextJoin ("", true, if (isError (mid (cell, sequence (len (ô 1) *1), mid (cell, sequence (LEN (cell)), 1), "")) Ví dụ: để các số dải từ một chuỗi trong A2, công thức là:
Hoặc
Kết quả là, tất cả các số được xóa khỏi một ô và các ký tự văn bản được giữ: Như được hiển thị trong ảnh chụp màn hình ở trên, công thức dải các ký tự số từ bất kỳ vị trí nào trong một chuỗi: lúc đầu, cuối cùng và ở giữa. Tuy nhiên, có một cảnh báo: nếu một chuỗi bắt đầu bằng một số theo sau là một không gian, không gian đó được giữ lại, tạo ra một vấn đề về không gian hàng đầu (như trong B2).starts with a number followed by a space, that space is retained, which produces a problem of leading spaces (like in B2). Để loại bỏ các không gian bổ sung trước khi văn bản, hãy bọc công thức trong hàm trang trí như thế này:spaces before text, wrap the formula in the TRIM function like this:
Bây giờ, kết quả của bạn là hoàn toàn hoàn hảo! Công thức này hoạt động như thế nào: Về bản chất, công thức hoạt động giống như được giải thích trong ví dụ trước. Sự khác biệt là, từ mảng cuối cùng được phục vụ cho chức năng TextJoin, bạn cần xóa số, không phải văn bản. Để thực hiện nó, chúng tôi sử dụng sự kết hợp của các hàm IF và ISError. Như bạn nhớ, Mid (Vượt) +0 tạo ra một loạt các số và #Value! Lỗi đại diện cho các ký tự văn bản ở cùng một vị trí:
Hàm isError bắt được các lỗi và chuyển mảng kết quả của các giá trị boolean cho nếu:
Khi hàm if nhìn thấy đúng (một lỗi), nó sẽ chèn ký tự văn bản tương ứng vào mảng được xử lý với sự trợ giúp của hàm giữa khác. Khi hàm if nhìn thấy sai (một số), nó sẽ thay thế nó bằng một chuỗi trống:
Mảng cuối cùng này được chuyển sang textjoin, vì vậy nó kết hợp các ký tự văn bản và đưa ra kết quả. Hàm tùy chỉnh để xóa số khỏi văn bảnGiải pháp hoạt động cho tất cả các phiên bản Excel Hãy nhớ rằng một công thức mạnh mẽ nên được giữ đơn giản, tôi sẽ chia sẻ mã của hàm do người dùng xác định (UDF) để loại bỏ bất kỳ ký tự số nào. Mã VBA 1: Hàm loại bỏ (asstring str) Dim sres asstring sres = "" cho i = 1 đến len (str) iffalse = isnumeric (mid (str, i, 1)) sau đó sres = sres & amp; Mid (str, i, 1) endifnext i reolenumbers = sres endfeft RemoveNumbers(str As String) Dim sRes As String sRes = "" For i = 1 To Len(str) If False = IsNumeric(Mid(str, i, 1)) Then sRes = sRes & Mid(str, i, 1) End If Next i RemoveNumbers = sRes End Function Mã VBA 2: Hàm RemoveNUMBERS (STR ASSTRING) AsstringWithCreateObject ("VBScript.Regexp") .global = true .Potype = "[0-9]" RemoveNUMBER RemoveNumbers(str As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[0-9]" RemoveNumbers2 = .Replace(str, "") End With End Function Như trường hợp của chức năng RemoveText, mã thứ hai tốt hơn để được sử dụng trong các bảng tính lớn để tối ưu hóa hiệu suất. Khi mã được thêm vào sổ làm việc của bạn, bạn có thể xóa tất cả các ký tự số khỏi ô bằng hàm tùy chỉnh này: RemoveNumbers(string) Trong trường hợp của chúng tôi, công thức trong B2 là:
Để cắt các không gian hàng đầu nếu có, hãy làm tổ chức năng tùy chỉnh bên trong trang trí giống như bạn sẽ có một công thức gốc:
Chia số và văn bản thành các cột riêng biệtTrong tình huống khi bạn muốn tách văn bản và số thành hai cột, thật tuyệt khi hoàn thành công việc với một công thức duy nhất, đồng ý? Đối với điều này, chúng tôi chỉ hợp nhất mã của các hàm xóa và loại bỏ các hàm thành một hàm, được đặt tên là splittextnumbers, hoặc đơn giản là chia, hoặc bất cứ điều gì bạn thích :) Mã VBA 1: Hàm loại bỏ (asstring str) Dim sres asstring sres = "" cho i = 1 đến len (str) iffalse = isnumeric (mid (str, i, 1)) sau đó sres = sres & amp; Mid (str, i, 1) endifnext i reolenumbers = sres endfeft SplitTextNumbers(str As String, is_remove_text As Boolean) As String Dim sNum, sText, sChar As String sCurChar = sNum = sText = "" For i = 1 To Len(str) sCurChar = Mid(str, i, 1) If True = IsNumeric(sCurChar) Then sNum = sNum & sCurChar Else sText = sText & sCurChar End If Next i If True = is_remove_text Then SplitTextNumbers = sNum Else SplitTextNumbers = sText End If End Function Mã VBA 2: Hàm RemoveNUMBERS (STR ASSTRING) AsstringWithCreateObject ("VBScript.Regexp") .global = true .Potype = "[0-9]" RemoveNUMBER SplitTextNumbers(str As String, is_remove_text As Boolean) As String With CreateObject("VBScript.RegExp") .Global = True If True = is_remove_text Then .Pattern = "[^0-9]" Else .Pattern = "[0-9]" End If SplitTextNumbers = .Replace(str, "") End With End Function Như trường hợp của chức năng RemoveText, mã thứ hai tốt hơn để được sử dụng trong các bảng tính lớn để tối ưu hóa hiệu suất. SplitTextNumbers (Chuỗi, IS_REMOVE_TEXT) Trong đó IS_REMOVE_TEXT là một giá trị boolean cho biết các ký tự nào sẽ dải:
Đối với bộ dữ liệu mẫu của chúng tôi, các công thức có biểu mẫu này: Để xóa các ký tự không phải là số:
Để xóa các ký tự số:
Mẹo. Để tránh một vấn đề tiềm năng của các không gian hàng đầu, tôi khuyên bạn nên luôn luôn kết thúc công thức loại bỏ các số trong hàm trang trí: To avoid a potential problem of leading spaces, I recommend always wrapping the formula that removes numbers in the TRIM function: Đối với những người không thích làm phức tạp những thứ một cách không cần thiết, tôi sẽ thể hiện cách riêng của chúng tôi để loại bỏ văn bản hoặc số trong Excel. Giả sử bộ cuối cùng của chúng tôi được thêm vào ruy băng excel của bạn, đây là những gì bạn làm:
Mẹo. Nếu kết quả chứa một số không gian hàng đầu, công cụ trang trí sẽ loại bỏ chúng ngay lập tức. If the results contain some leading spaces, the Trim Spaces tool will eliminate them in no time. Đó là cách xóa văn bản hoặc ký tự số khỏi chuỗi trong Excel. Tôi cảm ơn bạn đã đọc và mong được gặp bạn trên blog của chúng tôi vào tuần tới! Tải xuống có sẵnXóa văn bản hoặc số trong Excel - Ví dụ (tệp .xlsm) Ultimate Suite - Phiên bản dùng thử (tệp .exe) Bạn cũng có thể quan tâm |
