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

Hướng dẫn này sẽ dạy bạn cách tách văn bản khỏi 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.

Hãy tưởng tượng điều này: bạn nhận được 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. Trong hầu hết các trường hợp, chắc chắn sẽ thuận tiện hơn nếu để 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, 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í. Nhưng đó là một kịch bản lý tưởng cho các thử nghiệm trong phòng thí nghiệm. Trong cuộc sống thực, 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. Các ví dụ dưới đây cung cấp các 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 ô Excel

Giải pháp hoạt động trong 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 (ô, ROW (INDIRECT (“1:” & LEN (ô))), 1) + 0, “”))

Trong Excel 365, cái này cũng sẽ hoạt động:

TEXTJOIN (“”, TRUE, IFERROR (MID (ô, SEQUENCE (LEN (ô)), 1) + 0, “”))

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 hiệu quả

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 qua nhiều ô nếu cần.

Trong Excel 365 và 2019:

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

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 Đi vào Chìa khóa.

Trong Excel 365:

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

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ông thức Excel để loại bỏ văn bản và để lại số

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

Để 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)

Các MID hàm 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"}

Đối với mảng này, chúng tôi thêm số không. Các giá trị số tồn tại mà không thay đổi, trong khi việc thêm một số vào 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!}

Các IFERROR hàm 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 phục vụ cho hàm TEXTJOIN, hàm này sẽ nối các giá trị không trống trong mảng (ignore_empty đối số đượ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;"";"";"";"";"";"";"";"";"";"";"";""})

Tiền boa. Đố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.

Chức năng tùy chỉnh để xóa văn bản khỏi 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ớ, 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, chẳng hạn 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 (chuỗi)

Ví dụ: để xóa các ký tự văn bản khỏi một chuỗi trong 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:
Chức năng tùy chỉnh để loại bỏ văn bản và giữ số trong Excel

Ghi chú. Cả công thức gốc và hàm tùy chỉnh đều xuất ra 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 (ô, ROW (INDIRECT (“1:” & LEN (ô))), 1) +0), MID (ô, ROW (INDIRECT (“1:” & LEN (ô))), 1), “”))

Trong Excel 2019, hãy nhớ làm cho nó trở thành công thức mảng bằng cách nhấn Ctrl + Shift + Enter các phím với nhau.

Đối với Excel 365:

TEXTJOIN (“”, TRUE, IF (ISERROR (MID (ô, SEQUENCE (LEN (ô 1) +0), MID (ô, SEQUENCE (LEN (ô)), 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) +0), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Hoặc là

=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1)+0), 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:
Công thức Excel để xóa số khỏi chuỗi văn bản

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à dấu cách, không gian đó được giữ lại, điều này tạo ra vấn đề về khoảng trắng hàng đầu (như trong B2).

Để loại bỏ thêm khoảng trắng trước văn bản, bọc công thức trong hàm TRIM như sau:

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1)+0), 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!
Xóa số và cắt khoảng trắng trước văn bản

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ả.

Chức năng tùy chỉnh để xóa số khỏi văn bản

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 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 (chuỗi)

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))

Hàm tùy chỉnh để xóa số khỏi một chuỗi trong Excel

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ỉ hợp nhất mã của RemoveText và RemoveNumbers các chức năng thành một chức năng, được đặt tên SplitTextNumbers, hoặc đơn giản Tách ra, hoặc bất cứ điều 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)

Ở đâu 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 văn bản:

=SplitTextNumbers(A2, TRUE)

Để xóa số:

=SplitTextNumbers(A2, FALSE)
Tách số và văn bản thành các cột riêng biệt

Tiền boa. Để 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))

Đối với những người không thích làm phức tạp mọi thứ một cách không cần thiết, tôi sẽ chỉ ra cách riêng của chúng tôi để loại bỏ văn bản hoặc số trong Excel.

Giả sử của chúng tôi Suite Ultimate được thêm vào ruy-băng Excel của bạn, đây là những gì bạn làm:

Trên Dữ liệu Ablebits tab, trong Bản văn nhóm, nhấp vào Tẩy > Xóa ký tự.
Trên ngăn của bổ trợ, chọn phạm vi nguồn, chọn Xóa bộ ký tự tùy chọn và chọn một trong hai Bản văn nhân vật hoặc là Số nhân vật trong danh sách thả xuống.
Đánh Tẩy và tận hưởng thành quả