Địa chỉ, mã hàng hoặc ghi chú thường có số và chữ lẫn lộn, và vị trí của số thay đổi ở mỗi dòng. Tách số trong Excel trong trường hợp này không làm được bằng LEFT hoặc RIGHT đơn thuần, vì bạn không biết trước số nằm ở đâu.
Bài viết này hướng dẫn 3 cách, kèm cách đổi kết quả sang số thật và cách giữ lại phần chữ. Bài không kèm file vì bạn chỉ cần tự gõ bảng. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.
Bạn cần gõ bảng này trước khi làm theo
Bạn gõ bảng dưới đây vào một sheet trống, bắt đầu từ ô A1:
Nội dung | Kết quả |
|---|---|
234 Nguyễn Trãi | |
Phòng 305A | |
Giá 1500 đồng | |
Mã SP12 lô 7 | |
Không có số | |
007 Bond |

Ở mỗi mục, bạn gõ công thức vào ô B2 rồi nhấn Enter. Sau đó, bạn chọn lại B2, đưa chuột vào chấm vuông nhỏ ở góc dưới bên phải ô cho đến khi con trỏ thành dấu cộng mảnh màu đen, rồi kéo xuống B7. Khi sang mục mới, bạn gõ đè công thức mới lên cột B.
Nếu Excel báo lỗi khi bạn gõ dấu phẩy trong công thức, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi mọi dấu phẩy ngăn cách trong công thức thành dấu chấm phẩy.
Cách 1: Hàm TEXTJOIN và SEQUENCE giữ lại từng chữ số
Cách này dùng được trên Excel 365. Bạn gõ công thức sau vào B2 và kéo xuống B7:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))
Các dòng lần lượt trả về 234, 305, 1500, 127, ô trống và 007.

Công thức này đi từ trong ra ngoài như sau:
Phần
MID(A2,SEQUENCE(LEN(A2)),1)tách ô thành từng ký tự riêng.Phép nhân với 1 giữ nguyên chữ số, và làm ký tự khác báo lỗi.
Hàm IFERROR đổi các ô lỗi thành rỗng.
Hàm TEXTJOIN nối các ký tự còn lại thành một chuỗi.
Cách 2: Công thức dùng cho Excel 2019
Excel 2019 có hàm TEXTJOIN nhưng không có SEQUENCE, nên bạn thay bằng ROW và INDIRECT. Bạn gõ công thức sau vào B2:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))
Trên Excel 2019, bạn nhấn Ctrl + Shift + Enter thay vì Enter, rồi kéo xuống B7. Nếu quên tổ hợp phím này, công thức có thể cho kết quả sai mà không báo lỗi. Công thức đã được thử bằng cách nhập này trên Excel 365 và cho kết quả giống cách 1, nhưng chưa được thử trên máy cài Excel 2019.
Cách 3: Hàm REGEXREPLACE ngắn gọn nhất
Excel 365 bản mới có hàm REGEXREPLACE để thay thế theo mẫu. Bạn gõ công thức sau vào B2 và kéo xuống B7:
=REGEXREPLACE(A2,"[^0-9]","")
Kết quả giống hệt cách 1. Phần [^0-9] nghĩa là mọi ký tự không phải chữ số, và công thức thay chúng bằng rỗng.

Nếu Excel báo lỗi #NAME?, phiên bản của bạn chưa có hàm này. Khi đó, bạn dùng cách 1 hoặc cách 2.
Bạn đổi kết quả thành số thật bằng hàm VALUE
Kết quả của cả 3 cách đều là chữ. Công thức SUM sẽ bỏ qua chúng khi cộng cả vùng. Bạn bọc thêm hàm VALUE và gõ công thức sau vào B2, rồi chỉ kéo xuống B5. Bạn cũng xóa B6:B7 để bảng giống ảnh, vì hai dòng đó được giải thích riêng ở dưới.
=VALUE(TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")))
Các dòng trả về 234, 305, 1500 và 127, căn phải vì đã là số.

Nếu kéo tiếp xuống B6, dòng "Không có số" sẽ báo #VALUE! vì không có gì để đổi. Bạn có thể tránh lỗi này bằng công thức =IFERROR(VALUE(TEXTJOIN("",TRUE,IFERROR(MID(A6,SEQUENCE(LEN(A6)),1)*1,""))),""). Dòng 007 sẽ thành 7, nên bạn không nên dùng VALUE với mã cần giữ số 0 ở đầu.
Bạn giữ lại phần chữ bằng cách đảo điều kiện
Bạn có thể làm ngược lại để bỏ số và giữ chữ. Bạn gõ công thức sau vào B2 và kéo xuống B7:
=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1),MID(A2,SEQUENCE(LEN(A2)),1),"")))
Các dòng lần lượt trả về Nguyễn Trãi, Phòng A, Giá đồng, Mã SP lô, Không có số và Bond. Hàm TRIM xóa các khoảng trắng thừa còn lại sau khi bỏ số.

Bạn thường gặp những lỗi nào khi tách số?
Nhiều số bị dính lại: Dòng "Mã SP12 lô 7" trả về 127 vì công thức nối mọi chữ số. Trên Excel 365 bản mới, công thức
=REGEXEXTRACT(A5,"[0-9]+",1)tách riêng thành 12 và 7.Số thập phân bị mất dấu: Chuỗi "Giá 12.5kg" trả về 125, vì dấu chấm không phải chữ số. Bạn cần kiểm tra lại nếu dữ liệu có số lẻ.
Hàm SUM trả về 0: Kết quả đang là chữ. Bạn cần bọc hàm VALUE trước khi cộng cả vùng.
Hàm VALUE báo lỗi #VALUE!: Ô không có chữ số nào nên chuỗi kết quả bị rỗng. Bạn nên bọc IFERROR.
Người đọc thường hỏi gì về tách số trong Excel?
Khi nào tôi nên dùng LEFT hoặc RIGHT?
Bạn nên dùng LEFT hoặc RIGHT khi vị trí số cố định, như mã luôn có 2 ký tự đầu là chữ. Bài hàm xử lý chuỗi trong Excel hướng dẫn các trường hợp này.
Tôi có thể đổi kết quả chữ sang số bằng cách khác không?
Bạn có thể cộng thêm 0 hoặc nhân với 1, vì Excel tự đổi chữ số thành số khi tính toán. Cách này sẽ báo #VALUE! với ô rỗng. Bài chuyển text thành số trong Excel có thêm nhiều cách khác.
Bạn muốn dùng Excel thành thạo cho công việc văn phòng?
Khi biết cách tách từng ký tự rồi xử lý, bạn sẽ giải quyết được nhiều bài toán làm sạch dữ liệu khác nữa.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.