Đị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

Bảng gồm cột Nội dung có số và chữ lẫn lộn, cột Kết quả còn trống
Bảng thực hành nằm trong vùng A1:B7.

Ở 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 TEXTJOIN và SEQUENCE tách các chữ số ở cột B
Kết quả căn trái vì đang là chữ, nên số 0 ở đầu của 007 vẫn còn.

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.

Công thức REGEXREPLACE xóa mọi ký tự không phải chữ số
Hàm REGEXREPLACE cho công thức ngắn nhất, nhưng cần bản Excel 365 mới.

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

Công thức VALUE đổi kết quả tách được thành số thật
Kết quả căn phải cho thấy Excel đã hiểu đây 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ố.

Công thức giữ lại phần chữ và bỏ phần số ở cột B
Công thức giữ các ký tự làm phép nhân báo lỗi, tức là các ký tự không phải chữ 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.