Hôm nay trong bài này, chúng ta sẽ trình bày cách xóa ký tự số khỏi ô trong Excel. Đây là vấn đề rất phổ biến đặc biệt khi xử lý dữ liệu thô. Số và văn bản trộn lẫn trong ô và đôi khi cần tách hai giá trị khác biệt này. Có nhiều công thức xóa ký tự số khỏi ô trong Excel. Chúng ta sẽ học những cách dễ nhất để hoàn thành công việc.
Tải file thực hành này để tập luyện khi đọc bài viết.
5 phương pháp dễ dàng xóa ký tự số khỏi ô trong Excel
Hãy tưởng tượng khi nhận tập dữ liệu thô để phân tích và thấy số lẫn lộn với văn bản trong một cột. Để làm việc thuận tiện, cần tách chúng ra. Ở phần này, chúng ta sẽ học 5 phương pháp phù hợp.
1. Áp dụng hàm TEXTJOIN để xóa ký tự số khỏi ô
Bạn có thể dùng công thức dựa trên hàm TEXTJOIN để xóa ký tự số. Trước hết, tạo cột mới bên cạnh cột hiện tại để trích xuất kết quả. Chúng ta sẽ thảo luận toàn bộ thao tác từng bước. Hãy theo dõi!
Bước 1:
- Ở ô C4, áp dụng công thức dựa trên TEXTJOIN. Công thức như sau:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ROW(INDIRECT("1:100")),1)+0),MID(B4,ROW(INDIRECT("1:100")),1),""))- Ở đây dùng hàm MID, theo sau hàm ROW và hàm INDIRECT để tách các ký tự trong chuỗi văn bản.
- Hàm MID trích xuất văn bản trong B4 từng ký tự một.
MID(B4,ROW(INDIRECT("1:100")),1)- Ở đây 100 đại diện cho số lượng ký tự tối đa xem xét. Hàm MID này tạo mảng chứa 100 số như sau:
{1,2,3,4,5,6,7,8….99,100}
- Dùng mảng này làm đối số start_num và cho num_chars dùng 1.
- Giá trị văn bản số như 1,2,3 sẽ chuyển đổi không lỗi, còn ký tự không phải số sẽ không làm được. Kết quả trả về #Value. Dùng hàm IF và hàm ISERR bắt lỗi và đưa giá trị đó vào mảng hiện có.
- Nếu không lỗi, tức là số, chèn chuỗi rỗng ("") vào mảng thay cho số.
- Kết quả cuối vào hàm TEXTJOIN làm text1. Với dấu phân cách dùng chuỗi rỗng ("") và ignore_empty cung cấp TRUE.
- TEXTJOIN trả về kết quả nối tất cả giá trị không rỗng trong mảng.
Bước 2:
- Vì đây là công thức mảng, nhấn CTRL+SHIFT+ENTER để ra kết quả. Nếu dùng Excel 365, chỉ nhấn ENTER.
Bước 3:
- Di chuột đến góc dưới bên phải ô và nhấp đúp áp dụng công thức cho tất cả ô trong tập dữ liệu.
Đọc thêm: Cách xóa ký tự không phải số khỏi ô trong Excel
2. Chèn hàm SEQUENCE xóa ký tự số khỏi ô
Bước 1:
- Bạn có thể dùng công thức dựa trên hàm SEQUENCE nếu dùng Excel 365. Công thức này ngắn gọn hơn phương pháp trên. Công thức:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B4,SEQUENCE(LEN(B4)),1)+0),MID(B4,SEQUENCE(LEN(B4)),1),""))- Hàm SEQUENCE thay thế ROW và INDIRECT phục vụ cùng mục đích.
- Trong công thức này, dùng SEQUENCE và LEN xây dựng mảng độ dài chính xác trong một bước.
Bước 2:
- Nhấn ENTER lấy kết quả.
- Áp dụng công thức cho các ô còn lại đạt kết quả cuối.
Đọc thêm: Cách xóa ký tự trong Excel (6 phương pháp)
3. Dùng hàm LET xóa ký tự số khỏi ô
Bước 1:
- Chúng ta có thể tinh chỉnh công thức với hàm LET. Công thức:
=LET(ARRAY,SEQUENCE(LEN(B4)),TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ARRAY,1)+0),MID(B4,ARRAY,1),"")))- Dùng LET không tạo mảng hai lần, xác định mảng làm biến và tạo một lần.
Bước 2:
- Nhấn ENTER áp dụng công thức cho các ô cần thiết. Kết quả cuối ở đây.
Nội dung liên quan: Cách xóa ký tự từ bên trái trong Excel (6 cách)
Bài đọc tương tự:
- Cách xóa dấu ngoặc kép ẩn trong Excel (6 cách dễ dàng)
- Cách xóa dấu nháy đơn trong Excel (6 cách)
- Xóa ký tự cuối chuỗi trong Excel với VBA (2 cách dễ dàng)
- Cách xóa ký tự không in được trong Excel (4 cách dễ dàng)
- Xóa ký tự đầu chuỗi trong Excel (6 cách nhanh)
4. Dùng hàm TRIM xóa ký tự số khỏi ô
Đôi khi bạn thấy khoảng trắng thừa giữa dữ liệu thô lẫn văn bản và số. Hàm TRIM giúp giải quyết bằng cách xóa khoảng trắng thừa. Làm theo các bước dưới để học phương pháp này.
Bước 1:
- Ở ô C4, áp dụng công thức. Công thức:
=TRIM(TEXTJOIN("",TRUE,IF(ISERR(MID(B4,ROW(INDIRECT("1:100")),1)+0),MID(B4,ROW(INDIRECT("1:100")),1),"")))Ở đây không thay đổi gì mà thêm TRIM trước công thức TEXTJOIN phương pháp 1. Cũng có thể dùng công thức dựa trên SEQUENCE hoặc LET.
Bước 2:
- Nhấn ENTER lấy kết quả. Giờ thấy vấn đề khoảng trắng thừa đã giải quyết.
Nội dung liên quan: Cách xóa khoảng trắng trong Excel: Công thức, VBA & Power Query
5. Chạy mã VBA xóa ký tự số khỏi ô
Bạn có thể dùng mã macro VBA xóa ký tự số khỏi ô trong Excel. Chúng ta sẽ trình bày phương pháp này dưới.
Bước 1:
- Mở cửa sổ VBA macro. Nhấn Alt+11.
- Cửa sổ mới mở. Nhấp Insert chọn Module mở module mới.
Bước 2:
- Trong module mới, chèn mã VBA xóa ký tự số. Chúng ta cung cấp mã. Bạn sao chép dùng cho worksheet. Mã:
Sub RemoveNumber()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "VBA code to remove number"
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 3:
- Chạy mã. Chọn phạm vi, nhấp ok kết thúc thao tác.
- Chúng ta đã xóa thành công ký tự số khỏi ô.
Đọc thêm: Cách xóa ký tự đầu tiên khỏi chuỗi trong Excel với VBA
Lưu ý
👉 Nếu dùng công thức mảng, nhấn CTRL+SHIFT+ENTER cùng lúc để ra kết quả. Khi dùng Excel 365, chỉ nhấn ENTER.
👉 Hàm SEQUENCE chỉ có trong Excel 365. Không dùng được nếu dùng phiên bản Excel cũ.
👉 Hàm TEXTJOIN hoạt động cho người dùng Excel 2019. Người dùng phiên bản cũ không dùng được.
Kết luận
Chúng ta đã xem 5 cách khác nhau xóa ký tự số khỏi ô trong Excel. Rất hoan nghênh bình luận nếu có câu hỏi. Bạn cũng xem các bài viết khác liên quan tác vụ Excel!
Các bài viết liên quan
- Cách xóa dấu nháy kép trong Excel (5 phương pháp dễ dàng)
- Cách xóa dấu ngoặc đơn trong Excel (4 cách dễ dàng)
- Xóa ký tự sau ký tự cụ thể trong Excel (4 thủ thuật)
- Cách xóa dấu chấm phẩy trong Excel (4 phương pháp)
- Cách xóa ký tự không phải chữ số trong Excel (2 phương pháp)