Cách Xóa Văn Bản Khỏi ô Excel Chỉ Giữ Lại Số Và Ngược Lại

Bài viết này sẽ hướng dẫn bạn cách tách văn bản trong Excel và chỉ giữ lại số 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 là một người mới học hoặc sử dụng excel để làm việc, nếu một ngày bạn nhận được một tệp dữ liệu thô để phân tích và phát hiện ra rằng các số được trộn với văn bản trong một cột. Và điều đó chắc chắn khiến cho bạn khó khăn trong việc kiểm tra để đối chiếu với dữ liệu khác.

Nếu may mắn, bạn đang làm việc với dữ liệu đồng nhất, bạn có thể sử dụng các hàm LEFT, RIGHT và MID để trích xuất cùng một số ký tự từ cùng một vị trí. Tuy nhiên, điều này là rất hiếm khi xảy ra bởi nếu dễ thế thì bất cứ ai cũng có thể làm được mà không cần đến bạn.

Trên thực tế, bạn có nhiều khả năng xử lý các dữ liệu khác nhau trong đó các số đứng trước văn bản, sau văn bản hoặc giữa các văn bản. Hôm nay, blog sharekynang sẽ cung cấp các giải pháp chính xác cho trường hợp này thông qua các ví dụ cụ thể.

Bắt đầu nào..

Cách xóa văn bản và giữ số trong ô Excel

Đối với Excel 365 và Excel 2019:

Microsoft Excel 2019 đã giới thiệu một số hàm 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 các hàm như vậy, cụ thể là TEXTJOIN , để tách các ký tự văn bản khỏi ô chứa số.

Công thức chung là:

TEXTJOIN("", TRUE, IFERROR(MID(cell, ROW(INDIRECT( "1:"&LEN(cell))), 1) *1, ""))

Trong Excel 365, hàm này cũng sử dụng được:

TEXTJOIN("", TRUE, IFERROR(MID(cell, SEQUENCE(LEN(cell)), 1) *1, ""))

Chờ chút nào, nhìn công thức có vẻ hơi rắc rối đúng không? Nhưng tin tôi đi, nó hoạt động rất hiệu quả đấy!

Ví dụ: Để xóa văn bản khỏi các số trong A2, hãy nhập một trong các công thức dưới đây vào B2, sau đó kéo nó xuống các ô khác nếu cần.

Trong Excel 365 và 2019:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2))), 1) *1, ""))

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 Excel 365, 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.

Trong Excel 365:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

Do đó, tất cả các ký tự văn bản bị xóa khỏi một ô và các số được giữ lại:

Cách thức hoạt động của công thức này:

Để tôi giúp bạn 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 ROW (INDIRECT (“1:” & LEN (string))) hoặc SEQUENCE (LEN (string)) để tạo chuỗi một số tương ứng với tổng số ký tự trong chuỗi nguồn, sau đó cấp các số tuần tự đó cho hàm MID như các số bắt đầu. Trong ô B2, phần này của công thức trông như sau:

MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}, 1)

Hàm MID trích xuất từng ký tự từ A2 bắt đầu bằng ký tự đầu tiên và trả về chúng dưới dạng một mảng:

{"2";"1";"0";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}

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 số sẽ dẫn đến lỗi #VALUE! lỗi:

{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Hàm IFERROR xử lý các lỗi này và thay thế chúng bằng các chuỗi trống:

{2;1;0;"";"";"";"";"";"";"";"";"";"";"";""}

Mảng cuối cùng này được cung cấp cho hàm TEXTJOIN, hàm này sẽ nối các giá trị không trống trong mảng ( đối số ignore_empty được đặt thành TRUE) bằng cách sử dụng một chuỗi trống (“”) cho dấu phân cách:

TEXTJOIN("", TRUE, {2;1;0;"";"";"";"";"";"";"";"";"";"";"";""})

Cách xóa văn bản khỏi số sử dụng code VBA

Lưu ý: 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ớ, thì không có gì ngăn cản bạn tạo hàm 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 . Hàm do người dùng xác định (UDF) có thể được viết theo hai cách:

Mã VBA 1:

Ở đây, chúng ta xem xét từng ký tự trong chuỗi nguồn 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ả.

Function 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 một đối tượng để xử lý một biểu thức chính quy. Sử dụng RegExp, chúng tôi loại bỏ tất cả các ký tự không phải là chữ số 0-9 khỏi chuỗi nguồn.

Function RemoveText(str As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[^0-9]" RemoveText = .Replace(str, "") End With End Function

Trên các trang tính nhỏ, cả hai mã sẽ hoạt động tốt như nhau. Trên các trang tính lớn nơi hàm được gọi hàng trăm hoặc hàng nghì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ã vào sổ làm việc của bạn có thể được tìm thấy tại đây: Cách chèn mã VBA trong Excel .

Cho dù bạn chọn cách tiếp cận nào, từ góc độ người dùng cuối, chức năng xóa văn bản và để lại số đơn giản như sau:

RemoveText(string)

Ví dụ: để xóa các ký tự không phải số khỏi ô A2, công thức trong B2 là:

=RemoveText(A2)

Chỉ cần sao chép nó xuống cột và bạn sẽ nhận được kết quả sau:

Ghi chú. Cả công thức gốc và hàm tùy chỉnh đều xuất ra một chuỗi số . Để biến nó thành một số, hãy nhân kết quả với 1 hoặc cộng với số 0 hoặc bọc công thức trong hàm VALUE. Ví dụ:

=RemoveText(A2) + 0

=VALUE(RemoveText(A2))

Cách xóa số khỏi chuỗi văn bản trong Excel

Giải pháp hoạt động trong Excel 365 và Excel 2019

Các công thức để loại bỏ các số khỏi 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.

Đối với Excel 2019 và 365:

TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT( "1:"&LEN(cell) )), 1) *1), MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1), ""))

Trong Excel 2019, hãy nhớ đặt nó thành công thức mảng bằng cách nhấnCtrl + Shift + Enter các phím với nhau.

Đối với Excel 365:

TEXTJOIN("", TRUE, IF(ISERROR(MID(cell, SEQUENCE(LEN(cell 1) *1), MID(cell, SEQUENCE(LEN(cell)), 1), ""))

Ví dụ: để tách các số khỏi một chuỗi trong A2, công thức là:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Hoặc

=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), ""))

Do đó, tất cả các số bị xóa khỏi ô và các ký tự văn bản được giữ lại:

Như được hiển thị trong ảnh chụp màn hình ở trên, công thức tách các ký tự số từ bất kỳ vị trí nào trong một chuỗi: ở đầu, cuối và ở giữa. Tuy nhiên, có một lưu ý: nếu một chuỗi bắt đầu bằng một số theo sau là khoảng trắng, thì khoảng trắng đó sẽ được giữ lại, điều này tạo ra vấn đề về khoảng trắng ở đầu (như trong B2).

Để loại bỏ khoảng trắng thừa trước văn bản , hãy đặt công thức trong hàm TRIM như sau:

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))

Bây giờ, kết quả của bạn là hoàn toàn hoàn hảo rồi đó.

Cách thức hoạt động của công thức này:

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ân phối đến hàm TEXTJOIN, bạn cần xóa số, không xóa văn bản. Để hoàn thành, chúng tôi sử dụng kết hợp các hàm IF và ISERROR.

Như bạn nhớ, MID (…) +0 tạo ra một mảng số và #VALUE! lỗi biểu diễn các ký tự văn bản ở các vị trí giống nhau:

{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Hàm ISERROR bắt lỗi và chuyển kết quả mảng giá trị Boolean sang IF:

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

Khi hàm IF thấy TRUE (lỗi), nó sẽ chèn ký tự văn bản tương ứng vào mảng đã xử lý với sự trợ giúp của một hàm MID khác. Khi hàm IF nhìn thấy FALSE (một số), nó sẽ thay thế nó bằng một chuỗi trống:

{"";"";"";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}

Mảng cuối cùng này được chuyển cho TEXTJOIN, vì vậy nó sẽ nối các ký tự văn bản và xuất ra kết quả.

Cách xóa số khỏi văn bản sử dụng mã code VBA

Giả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:

Function 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:

Function 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 nên được sử dụng trong các trang tính lớn để tối ưu hóa hiệu suất.

Sau 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 một ô bằng cách sử dụ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à:

=RemoveNumbers(A2)

Để cắt bớt các khoảng trắng ở đầu nếu có, hãy lồng hàm tùy chỉnh vào bên trong TRIM giống như cách bạn làm với một công thức gốc:

=TRIM(RemoveNumbers(A2))

Chia số và văn bản thành các cột riêng biệt

Trong tình huống khi văn bản và số cần được tách thành các cột khác nhau, sẽ rất tuyệt nếu bạn thực hiện công việc với một công thức duy nhất, đồng ý không?

Đối với điều này, chúng tôi chỉ cần hợp nhất mã của các hàm RemoveText và RemoveNumbers thành một hàm, có tên là SplitTextNumbers , hoặc đơn giản là Split , hoặc bất cứ thứ gì bạn thích

Mã VBA 1:

Function 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:

Function 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

Hàm tùy chỉnh mới của chúng tôi yêu cầu hai đối số:

SplitTextNumbers (string, is_remove_text)

Trong đó is_remove_text là một giá trị Boolean cho biết những ký tự nào cần tách:

• TRUE hoặc 1 – xóa văn bản và giữ số
• FALSE hoặc 0 – xóa số và giữ lại văn bản

Đối với tập dữ liệu mẫu của chúng tôi, các công thức có dạng sau:

Để xóa các ký tự không phải số:

=SplitTextNumbers(A2, TRUE)

Để xóa các ký tự số:

=SplitTextNumbers(A2, FALSE)

Mẹo. Để tránh vấn đề tiềm ẩn về khoảng trắng ở đầu, tôi khuyên bạn nên luôn bao bọc công thức loại bỏ các số trong hàm TRIM:

=TRIM(SplitTextNumbers(A2, FALSE))

Tổng kết

Như vây, với việc sử dụng cách tách chữ trong ô Excel chỉ giữ lại số và ngược lại bạn có thể giải quyết được rất nhiều công việc phục vụ sắp xếp, so sánh và phân tích số liệu của mình rồi. Bạn hoàn toàn có thể ứng dụng giải pháp này cho các trường hợp khác cho hiệu quả nữa nhé.

Chúc các bạn thành công.