Cách xóa các số và những ký tự không cần thiết khác trong Excel

Giả sử bạn có một trang tính với nhiều dữ liệu như sau ABCD4578124YUIOH và bạn muốn xóa các ký tự không phải số ABCD, YUIOH nhưng vẫn giữ các ký tự số trong ô. Tất nhiên, bạn có thể xóa từng ký tự một, nhưng trong bài viết này, chúng mình sẽ hướng dẫn bạn cách nhanh chóng xóa những ký tự không mong muốn trong bảng tính Excel.

Nội dung chính

1 Cách xóa các ký tự không phải số trong Excel
2 Cách xóa các ký tự số ra khỏi ô trong Excel
3 Cách xóa các ký tự không phải chữ và số trong Excel

3.1 Xóa các ký tự không phải chữ và số bằng Hàm tùy chỉnh
3.2 Xóa các ký tự không phải chữ và số bằng mã VBA

4 Cách xóa các ký tự không thể in được trong Excel bằng hàm CLEAN

Cách xóa các ký tự không phải số trong Excel

Để xóa các ký tự không phải số khỏi một dải ô có mã VBA, bạn vui lòng thực hiện như sau:

Bước 1: Bấm Developer -> Visual Basic để mở cửa sổ Microsoft Visual Basic cho ứng dụng. Trong cửa sổ Microsoft Visual Basic for applications, bấm Insert -> Module, sau đó sao chép và dán đoạn mã sau vào modul:

VBA: Xóa tất cả các ký tự không phải số

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Sub RemoveNotNum()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Bước 2: Sau đó, nhấp vào phím run để chạy code của bạn. Tất cả những ký tự không phải số trong phạm vi bạn chọn sẽ bị xóa.

lenh-xoa-trong-excel — xóa các ký tự không phải số với VBA

Nếu các số có dấu thập phân, bạn có thể sử dụng VBA sau:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub RemoveNotNum()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9.]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Bạn có thể xem kết quả như ảnh chụp màn hình dưới đây:

Cách xóa các ký tự số ra khỏi ô trong Excel

Bây giờ, bạn có một trang tính chứa rất nhiều thông tin như vậy James987den , dữ liệu chứa cả ký tự chữ cái và ký tự số, bạn muốn giữ lại các ký tự chữ và loại bỏ các ký tự số cho tất cả các dữ liệu đó. Chẳng hạn như chuyển đổi James987den thành Jamesdenm, bạn phải làm thế nào?

Nếu bạn đã quen với mã VBA, bạn có thể sử dụng phương pháp sau:

Bước 1: Bấm Developer -> Visual Basic để mở cửa sổ Microsoft Visual Basic cho ứng dụng. Trong cửa sổ Microsoft Visual Basic for applications, bấm Insert > Module, sau đó sao chép và dán đoạn mã sau vào modul:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Sub RemoveNum()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9.]" Then
            xStr = ""
        Else
            xStr = xTemp
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Bước 2: Nhấn Ok tất cả các ký tự số đã bị xóa.

Cách xóa các ký tự không phải chữ và số trong Excel

Giả sử có một dải dữ liệu, chẳng hạn như JAMES0898 # 4% ^ {} , chứa các ký tự không phải chữ và số trong trang tính, bây giờ bạn chỉ muốn xóa các ký tự không phải chữ và số, giữ lại các ký tự chữ và số. Ví dụ: JAMES0898 # 4% ^ {} > JAMES0898 . Bạn có thể thực hiện một trong hai cách sau:

Xóa các ký tự không phải chữ và số bằng Hàm tùy chỉnh

Để xóa cá ký tự không cần thiết ra khỏi ô, bạn có thể tạo một hàm tùy chỉnh.

Bước 1: Bấm Developer > Visual Basic để mở cửa sổ Microsoft Visual Basic cho ứng dụng. Trong cửa sổ Microsoft Visual Basic for applications, bấm Insert > Module, sau đó sao chép và dán đoạn mã sau vào modul:

Hàm tùy chỉnh để xóa tất cả các ký tự không phải chữ và số

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Function DeleteNonAlphaNumeric(xStr As String) As String
Dim xStrR As String
Dim xCh As String

Dim xStrMode As String
Dim xInt As Integer
    xStrMode = "[A-Z.a-z 0-9]"
    xStrR = ""
    For xInt = 1 To Len(xStr)
        xCh = Mid(xStr, xInt, 1)
        If xCh Like xStrMode Then
            xStrR = xStrR & xCh
        End If
    Next
    DeleteNonAlphaNumeric = xStrR
End Function

Bước 2: Bây giờ quay lại trang tính, chọn một ô trống và nhập công thức sau:

= DeleteNonAlphaNumeric (B3) , sau đó kéo xuống để áp dụng công thức này cho các ô khác.

Xóa các ký tự không phải chữ và số bằng mã VBA

Ngoài ra, bạn có thể sử dụng mã VBA ngắn sau đây để xóa các ký tự không phải chữ và số ra khỏi ô của mình:

Bước 1: Bấm Developer > Visual Basic để mở cửa sổ Microsoft Visual Basic cho ứng dụng.

Bước 2: Trong cửa sổ Microsoft Visual Basic for applications, bấm Insert > Module, sau đó sao chép và dán đoạn mã sau vào modul:

VBA: Xóa tất cả các ký tự không phải chữ và số

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Sub RemoveNotAlphasNotNum()
'Updateby2014128
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Bước 3: Nhấn phím F5 hoặc nhấp vào nút Run để thực thi mã.

Bước 4: Sau đó, chọn một phạm vi bạn muốn xóa tất cả các ký tự không phải chữ và số trong hộp thoại bật lên và nhấp vào nút OK .

Bây giờ tất cả các ký tự không phải chữ và số đã bị xóa khỏi phạm vi đã chọn.

Cách xóa các ký tự không thể in được trong Excel bằng hàm CLEAN

Nếu bạn làm việc với các tệp đến từ một nguồn không phải Excel hoặc bạn dán thông tin từ một ứng dụng khác vào các ô trang tính, đôi khi bạn có một số ký tự không in được hoặc không hiển thị được trong trang tính của mình. Để làm cho trang tính của bạn sạch và đẹp hơn, bạn cần xóa các ký tự không cần thiết đó.

Hàm CLEAN có thể xóa tất cả các ký tự không thể in ra khỏi ô. Ví dụ, tôi có Cột C (C2: C11) cần loại bỏ các ký tự không in được. Bạn có thể làm như hướng dẫn sau:

Bước 1: Chọn một ô trống D2 liền kề với C2 và nhập: ” = CLEAN (C2) ”.

Bước 2: Nhấn phím Enter .

Bước 3: Và sau đó kéo công thức trong phạm vi ô mà bạn muốn xóa các ký tự không in được.

Hy vọng qua bài viết này, bạn đã biết cách xóa những ký tự không mong muốn trong Excel. Để đọc thêm những mẹo và thủ thuật khác trong Excel bạn hãy truy cập Blog Excel của Gitiho nhé.

Nếu bạn đang muốn tìm hiểu các kiến thức chuyên sâu về Excel và VBA thì đừng bỏ qua khóa học Excel - trở thành chuyên gia Excel từ con số 0 của Gitiho nhé. Đây là khóa học có lộ trình bài bản cung cấp trọn bộ kiến thức nền tảng trong Excel. Sau khi nắm được các kiến thức nền tảng, bạn sẽ được học toàn bộ kiến thức Excel VBA và cách ứng dụng thực tế của Excel VBA trong công việc. Bên cạnh đó bạn sẽ được những chuyên gia đào tạo kỹ năng Excel hàng đầu hỗ trợ giải đáp khi có thắc mắc để đảm bảo khi học xong bạn có thể áp dụng được ngay vào công việc của mình.

Gitiho chân thành cảm ơn bạn đọc!