Trong bài viết này, bạn sẽ tìm hiểu cách xóa các chữ cái khỏi ô trong Excel.
Tải xuống Workbook
Bạn có thể tải xuống sổ làm việc Excel thực hành miễn phí từ đây.
10 phương pháp xóa chữ cái khỏi ô trong Excel
Phần này sẽ thảo luận về cách xóa các chữ cái khỏi ô trong Excel bằng cách sử dụng công cụ lệnh của Excel, các công thức khác nhau, VBA vv
1. Xóa các chữ cái cụ thể khỏi ô bằng Tính năng tìm và thay thế trong Excel
Tìm &Thay thế lệnh là tính năng dễ nhất và phổ biến nhất để thực hiện hầu hết các tác vụ liên quan đến Excel. Ở đây, chúng ta sẽ biết cách xóa các ký tự bằng cách sử dụng Tìm &Thay thế tính năng trong Excel.
Hãy xem xét tập dữ liệu sau mà từ đó chúng tôi sẽ loại bỏ tất cả các chữ cái ( WWE ) từ các ô trong Mã cột chỉ để lại các số.
Các bước để làm điều đó được đưa ra dưới đây,
Các bước:
- Chọn tập dữ liệu.
- Trong Trang chủ , đi tới Tìm &Chọn -> Thay thế .
- Từ cửa sổ bật lên Tìm và Thay thế trong hộp Tìm gì trường, viết WWE.
- Rời khỏi mục Thay thế bằng trường trống .
- Nhấn Thay thế tất cả .
Thao tác này sẽ xóa tất cả WWE từ các ô trong tập dữ liệu của bạn trong Excel và chỉ để lại cho bạn những con số.
Đọc thêm: Cách xóa văn bản khỏi ô Excel (9 cách dễ dàng)
2. Xóa các chữ cái cụ thể khỏi ô bằng hàm SUBSTITUTE trong Excel
Không giống như Tìm &thay thế tính năng lệnh trong Excel, sử dụng công thức là cách an toàn nhất và được kiểm soát nhiều nhất để trích xuất bất kỳ loại kết quả nào trong Excel. Để nhận đầu ra của tập dữ liệu không có bất kỳ ký tự cụ thể nào trong Excel, bạn có thể triển khai hàm SUBSTITUTE .
SUBSTITUTE chung Công thức,
=SUBSTITUTE(cell, " old_text" , " new_text" ) Đây,
old_text =văn bản bạn muốn xóa
new_text =văn bản mà bạn muốn thay thế bằng
Dưới đây là cùng một tập dữ liệu mà chúng tôi đã sử dụng trong phần trên. Và lần này, thay vì sử dụng Tìm &thay thế để xóa các chữ cái, chúng tôi sẽ áp dụng SUBSTITUTE để có được kết quả đầu ra mong muốn.
Các bước:
- Trong một ô trống mà bạn muốn kết quả của mình xuất hiện, hãy viết công thức sau,
=SUBSTITUTE(C5,"WWE","") Đây,
C5 =ô chứa giá trị để loại bỏ các chữ cái
"WWE" =các chữ cái cần loại bỏ
"" =để thay thế “WWE” bằng chuỗi trống
- Nhấn Enter .
Nó sẽ thay thế tất cả WWE (hoặc bất kỳ văn bản nào khác mà bạn đã chọn) với một chuỗi rỗng (hoặc chuỗi mà bạn thay thế nó bằng).
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Bây giờ bạn đã tìm thấy kết quả của một tập dữ liệu các ô không có bất kỳ chữ cái nào.
Đọc thêm: Cách xóa văn bản cụ thể khỏi cột trong Excel (8 cách)
3. Trích xuất các chữ cái từ một phiên bản cụ thể từ ô trong Excel
Cho đến bây giờ chúng ta chỉ học cách xóa tất cả các chữ cái khỏi các ô. Nhưng điều gì sẽ xảy ra nếu bạn chỉ muốn xóa các chữ cái khỏi một vị trí nhất định của ô.
Chẳng hạn như, thay vì xóa tất cả WWE từ các ô, chúng tôi chỉ muốn giữ lại W đầu tiên cùng với các số từ mỗi ô.
Các bước:
- Cũng giống như phần ở trên, nơi chúng tôi đã triển khai SUBSTITUTE chức năng xóa WWE , ở đây chúng tôi sẽ chỉ xác định vị trí nhất định mà chúng tôi muốn xóa các ký tự.
Vì vậy, SUBSTITUTE ở trên công thức,
=SUBSTITUTE(C5,"WWE","") Trở thành,
=SUBSTITUTE(C5,"WE","",1) Đây, 1 có nghĩa là, chúng tôi muốn xóa W đầu tiên từ các ô trong tập dữ liệu của chúng tôi (nếu bạn muốn xóa ký tự thứ 2 khỏi tập dữ liệu của mình thì chỉ cần viết 2 thay vì 1, nếu bạn muốn xóa ký tự thứ 3 khỏi tập dữ liệu của mình thì chỉ cần viết 3 thay vì 1, v.v.).
- Nhấn Enter .
- Một lần nữa, chỉ cần kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Bây giờ bạn đã tìm thấy kết quả của tập dữ liệu các ô có W đầu tiên cùng với những con số.
4. Xóa nhiều chữ cái cụ thể khỏi ô bằng hàm SUBSTITUTE lồng nhau
SUBSTITUTE hàm chỉ loại bỏ các chữ cái cho bất kỳ số lượng phiên bản nào tại một thời điểm. Vì vậy, nếu bạn muốn xóa nhiều ký tự cùng một lúc thì bạn cần triển khai SUBSTITUTE lồng nhau chức năng.
Vì vậy, hãy cùng tìm hiểu cách triển khai SUBSTITUTE lồng nhau chức năng xóa nhiều chữ cái cùng một lúc.
Các bước:
- Để thiết lập SUBSTITUTE lồng nhau , bạn phải viết một SUBSTITUTE chức năng bên trong một SUBSTITUTE khác hàm và chuyển các đối số có liên quan vào bên trong dấu ngoặc.
Để hiểu thêm, hãy xem hình bên dưới,
Ở đâu,
Để xóa nhiều W từ Ô C5 , trước tiên chúng tôi viết công thức,
=SUBSTITUTE(C5,"W","")
và sau đó, để xóa E (hoặc bất kỳ chữ cái nào khác mà bạn yêu cầu) cùng với nó, chúng tôi đặt công thức này bên trong một SUBSTITUTE khác công thức và chuyển các đối số ( old_text, new_text ) bên trong nó (trong trường hợp của chúng tôi, nó là " E","" ).
Vì vậy, bây giờ công thức là,
=SUBSTITUTE(SUBSTITUTE(C5,"W",""),"E","") - Nhấn Enter .
Nó sẽ thay thế tất cả W và E (hoặc bất kỳ văn bản nào khác mà bạn đã chọn) bằng chuỗi rỗng (hoặc chuỗi mà bạn thay thế bằng). - Một lần nữa kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Bây giờ bạn đã tìm thấy kết quả của một tập dữ liệu các ô không có bất kỳ chữ cái nào.
5. Tách các chữ cái đầu tiên hoặc cuối cùng khỏi ô có công thức trong Excel
Trong phần này, bạn sẽ học cách xóa các chữ cái đầu tiên hoặc cuối cùng khỏi các ô bằng công thức trong Excel.
5.1 Xóa các chữ cái đầu tiên khỏi ô có công thức trong Excel
Các bước để xóa các chữ cái đầu tiên khỏi các ô có công thức trong Excel được đưa ra dưới đây.
Các bước:
- Đầu tiên, hãy chọn một ô mà bạn muốn hiển thị kết quả của mình.
- Trong ô, hãy viết công thức sau,
=RIGHT(C5, LEN(C5)-3) Đây,
C5 =ô để xóa các chữ cái
- Nhấn Enter .
Nó sẽ xóa các chữ cái từ đầu ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa tất cả các chữ cái từ đầu ô.
Phân tích Công thức
- LEN (C5) -> Chức năng LEN xác định độ dài của Ô C5
- Đầu ra: 6
- LEN (C5) -3 -> trở thành
- 6-3
- Đầu ra: 3
- PHẢI (C5, LEN (C5) -3) -> trở thành
- ĐÚNG (C5, 3)
- Đầu ra: 101
- Giải thích: Xóa 3 chữ cái đầu tiên từ Ô C5
5.2 Xóa các chữ cái cuối cùng khỏi ô có công thức trong Excel
Các bước để xóa các chữ cái cuối cùng khỏi các ô có công thức trong Excel được đưa ra dưới đây.
Các bước:
- Đầu tiên, hãy chọn một ô mà bạn muốn hiển thị kết quả của mình.
- Trong ô, hãy viết công thức sau,
=LEFT(C5, LEN(C5)-2) Đây,
C5 =ô để xóa các chữ cái
- Nhấn Enter .
Nó sẽ xóa các chữ cái ở cuối ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa tất cả các chữ cái ở cuối ô.
Phân tích Công thức
- LEN (C5) -> Độ dài của Ô C5
- Đầu ra: 6
- LEN (C5) -2 -> trở thành
- 6-2
- Đầu ra: 4
- LEFT (C5, LEN (C5) -2) -> trở thành
- TRÁI (C5, 2)
- Đầu ra: WWE1
- Giải thích: Xóa 2 cuối cùng các chữ cái từ Ô C5
Đọc thêm: Cách xóa văn bản sau ký tự trong Excel (3 cách)
6. Loại bỏ cả chữ cái đầu tiên và chữ cái cuối cùng khỏi ô có công thức trong Excel
Trong phần này, chúng tôi sẽ chỉ cho bạn cách xóa tất cả các chữ cái hiện có trong một ô.
Các bước để làm điều đó được đưa ra bên dưới.
Các bước:
- Đầu tiên, hãy chọn một ô mà bạn muốn hiển thị kết quả của mình.
- Trong ô, hãy viết công thức sau,
=MID(C5,3,LEN(C5)-4) Đây,
C5 =ô để xóa các chữ cái
- Nhấn Enter .
Nó sẽ xóa các chữ cái ở cả phần đầu và phần cuối của ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa tất cả các chữ cái từ đầu và cuối của ô.
Phân tích Công thức
- LEN (C5) -> Độ dài của Ô C5
- Đầu ra: 6
- LEN (C5) -4 -> trở thành
- 6-4
- Đầu ra: 2
- MID (C5,3, LEN (C5) -4) -> trở thành
- MID (C5,3,2)
- Đầu ra: E1
- Giải thích: Xóa 2 chữ cái cuối cùng từ Ô C5 bắt đầu từ vị trí của 3 với chức năng MID .
Đọc thêm: Cách xóa văn bản khỏi ô Excel (9 cách dễ dàng)
7. Xóa các chữ cái khỏi ô có công thức mảng trong Excel
Nếu bạn làm việc với một lượng lớn dữ liệu thì bạn cần một cách mạnh hơn để loại bỏ tất cả các chữ cái. Triển khai Mảng để vận hành một lượng lớn dữ liệu nhanh chóng và hiệu quả hơn.
Tại đây, chúng tôi sẽ chỉ cho bạn Mảng công thức xóa chữ cái khỏi ô trong Excel.
Các bước:
- Đầu tiên, hãy chọn một ô mà bạn muốn hiển thị kết quả của mình.
- Trong ô, hãy viết công thức sau,
=SUM(MID(0&C5,LARGE(INDEX(ISNUMBER(--MID(C5,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10) Đây,
C5 =ô để xóa các chữ cái
- Nhấn Enter .
Nó sẽ xóa tất cả các chữ cái khỏi các ô trong Excel.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa tất cả các ký tự khỏi tập dữ liệu các ô trong Excel.
Ghi chú:
- Công thức Mảng này sẽ xóa tất cả các loại ký tự bao gồm chữ cái, ký tự đặc biệt, v.v. ngoại trừ các ký tự số. Ví dụ:nếu chuỗi gốc là abc * 123-def, công thức này sẽ xóa tất cả các chữ cái và ký tự đặc biệt ngoại trừ số và trả về 123.
- Nếu không có ký tự số nào trong chuỗi ban đầu thì công thức này sẽ trả về 0.
Đọc thêm: Cách xóa văn bản khỏi ô Excel nhưng để lại số (8 cách)
8. Loại bỏ các chữ cái đầu tiên hoặc cuối cùng khỏi ô có chức năng do người dùng xác định (UDF) trong VBA
Triển khai Macro VBA là phương pháp hiệu quả nhất, nhanh nhất và an toàn nhất để chạy bất kỳ thao tác nào trong Excel. Trong phần này, chúng ta sẽ tìm hiểu cách sử dụng VBA để xóa các chữ cái khỏi các ô có chức năng do người dùng xác định (UDF) trong Excel.
8.1 Xóa các chữ cái đầu tiên khỏi ô bằng VBA trong Excel
Các bước để xóa các chữ cái đầu tiên khỏi ô bằng VBA UDF trong Excel được hiển thị bên dưới.
Các bước:
- Nhấn Alt + F11 trên bàn phím của bạn hoặc chuyển đến tab Nhà phát triển -> Visual Basic để mở Visual Basic Editor .
- Trong cửa sổ mã bật lên, từ thanh trình đơn, nhấp vào Chèn -> Mô-đun .
- Sao chép mã sau và dán vào cửa sổ mã.
Public Function DeleteFirstL(Irng As String, Icnt As Long)
DeleteFirstL = Right(Irng, Len(Irng) - Icnt)
End FunctionĐây không phải là một thủ tục phụ cho VBA chương trình để chạy, đây là tạo Hàm do người dùng xác định (UDF) . Vì vậy, sau khi viết mã, thay vì nhấp vào nút Chạy từ thanh trình đơn, nhấp vào Lưu .
- Bây giờ, hãy quay lại trang tính quan tâm và viết hàm bạn vừa tạo bằng VBA mã (Chức năng DeleteFirstL trong dòng đầu tiên của mã) và bên trong dấu ngoặc đơn của DeleteFirstL , chuyển số tham chiếu ô mà bạn muốn xóa các ký tự (trong trường hợp của chúng tôi, chúng tôi chuyển Ô B5 bên trong dấu ngoặc đơn) và số lượng mà bạn muốn loại bỏ chữ cái (chúng tôi muốn xóa 3 chữ cái đầu tiên nên chúng tôi đặt 3 ).
- Nhấn Enter .
Nó sẽ xóa các chữ cái từ đầu ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa 3 chữ cái từ đầu ô.
8.2 Xóa chữ cái cuối cùng khỏi ô bằng VBA trong Excel
Các bước để xóa các chữ cái cuối cùng khỏi ô bằng VBA UDF trong Excel được hiển thị bên dưới.
Các bước:
- Tương tự như trước đây, mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Trong cửa sổ mã, hãy sao chép và dán mã sau đây.
Public Function DeleteLastL(Irng As String, Icnt As Long)
DeleteLastL = Left(Irng, Len(Irng) - Icnt)
End Function
- Lưu mã và quay lại trang tính quan tâm và viết hàm bạn vừa tạo bằng VBA mã (Chức năng DeleteLastL trong dòng đầu tiên của mã) và bên trong dấu ngoặc đơn của DeleteLastL , chuyển số tham chiếu ô mà bạn muốn xóa các ký tự (trong trường hợp của chúng tôi, chúng tôi chuyển Ô B5 bên trong dấu ngoặc đơn) và số lượng số mà bạn muốn loại bỏ chữ cái (chúng tôi muốn xóa 2 chữ cái cuối cùng nên chúng tôi đặt 2 ).
- Nhấn Enter .
Nó sẽ xóa các chữ cái ở cuối ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Nó sẽ xóa 2 chữ cái cuối cùng ở cuối ô.
8.3 Xóa tất cả các chữ cái khỏi ô bằng VBA trong Excel
Bây giờ chúng ta sẽ tìm hiểu cách xóa tất cả các chữ cái khỏi các ô bằng VBA UDF trong Excel.
Các bước:
- Tương tự như trước đây, mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Trong cửa sổ mã, hãy sao chép và dán mã sau đây.
Function DeleteLetter(iTxt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
DeleteLetter = .Replace(iTxt, "")
End With
End Function
- Lưu mã và quay lại trang tính quan tâm và viết hàm bạn vừa tạo bằng VBA mã (Chức năng DeleteLetter trong dòng đầu tiên của mã) và bên trong dấu ngoặc đơn của DeleteLetter , chuyển số tham chiếu ô mà bạn muốn xóa các ký tự (trong trường hợp của chúng tôi, chúng tôi chuyển Ô B5 bên trong dấu ngoặc đơn).
- Nhấn Enter .
Nó sẽ xóa tất cả các chữ cái khỏi ô.
- Kéo hàng xuống bằng cách sử dụng Fill Handle để áp dụng công thức cho các ô còn lại.
Bạn sẽ nhận được một tập dữ liệu các ô được loại bỏ khỏi tất cả các chữ cái.
Đọc thêm: Cách xóa văn bản trước dấu cách bằng công thức Excel (5 phương pháp)
9. Xóa các chữ cái khỏi ô có Công cụ chuyển văn bản thành cột của Excel
Excel có một công cụ lệnh tích hợp được gọi là Văn bản thành cột . Chúng tôi có thể sử dụng công cụ này để xóa các chữ cái khỏi các ô trong Excel.
Các bước để làm điều đó được đưa ra bên dưới.
Các bước:
- Chọn các ô mà bạn muốn xóa các ký tự.
- Chuyển đến tab Dữ liệu -> Văn bản thành Cột
- Từ cửa sổ bật lên, chọn Chiều rộng cố định làm kiểu dữ liệu.
- Nhấp vào Tiếp theo .
- Tiếp theo trong Xem trước dữ liệu , kéo đường thẳng đứng cho đến khi bạn đạt được tất cả các chữ cái mà bạn muốn xóa (chúng tôi muốn xóa WWE vì vậy chúng tôi đã kéo dòng bao gồm tất cả WWE của giá trị dữ liệu).
- Nhấp vào Tiếp theo .
- Chọn Định dạng dữ liệu cột theo nhu cầu của bạn.
- Nhấp vào Kết thúc .
Bạn sẽ nhận được tất cả dữ liệu ngoại trừ các chữ cái trong một cột khác.
Bằng cách này, bạn có thể trích xuất các ký tự mà bạn muốn xóa khỏi các ô.
10. Xóa các chữ cái khỏi ô bằng cách sử dụng Flash Fill trong Excel
Bạn cũng có thể xóa các chữ cái khỏi các ô bằng cách sử dụng Flash Fill của Excel tính năng. Cái gì Tô flash là, đầu tiên nó tìm kiếm bất kỳ mẫu nào được cung cấp bởi người dùng và sau đó theo mẫu đó, nó sẽ điền vào các ô khác.
Các bước để xóa các chữ cái khỏi ô bằng cách sử dụng Flash Fill được đưa ra dưới đây. Chúng tôi sẽ mô tả bằng một ví dụ để bạn hiểu rõ hơn.
Các bước:
- Xem hình ảnh sau đây, nơi chúng tôi muốn xóa tất cả WWE từ Mã WWE101 . Vì vậy, ô bên cạnh nó, chúng tôi chỉ viết 101 để làm quen với Excel về mẫu mà chúng tôi muốn.
- Sau đó, chọn phần còn lại của các ô, chúng tôi nhấp vào Dữ liệu -> Flash Fill .
Nó sẽ lấp đầy tất cả các ô còn lại bằng cùng một mẫu mà chúng tôi đã cung cấp, loại bỏ WWE và chỉ để lại cho bạn những con số.
Bạn cũng có thể nhấn Phím tắt Ctrl + E để kích hoạt Flash Fill .
Đọc thêm: Cách xóa văn bản cụ thể khỏi ô trong Excel (11 cách dễ nhất)
Kết luận
Bài viết này đã hướng dẫn bạn cách xóa các chữ cái khỏi ô trong Excel theo 10 cách khác nhau. Tôi hy vọng bài viết này rất hữu ích cho bạn. Vui lòng hỏi nếu bạn có bất kỳ câu hỏi nào liên quan đến chủ đề này.
Các bài viết liên quan
- Cách xóa tên đã xác định trong Excel (3 cách)