Cách trích số từ chuỗi trong Excel: Chọn đúng cách tiếp cận

Khi làm việc trong Excel để làm sạch dữ liệu, bạn có thể gặp nhiệm vụ trích xuất các số từ chuỗi chữ-số. Bài toán này rất phổ biến nếu bạn có dữ liệu mà số được nhúng trong chuỗi, như mã sản phẩm hoặc phản hồi khảo sát.

Trong bài viết này, tôi sẽ chỉ cho bạn các phương pháp khác nhau để trích số từ chuỗi bằng các công thức như RIGHT(), LEN() và FIND() cho các trường hợp sử dụng khác nhau. Nếu bạn chưa quen với một số kiến thức cơ bản, tôi khuyến nghị bạn tham gia khóa Giới thiệu về Excel của chúng tôi để xây dựng nền tảng vững chắc. Bạn cũng nên xem Chuẩn bị Dữ liệu trong Excel để tìm hiểu thêm về cách biến đổi dữ liệu thô.

Công thức Excel để trích số từ một chuỗi

Excel cung cấp nhiều phương pháp dựa trên công thức để trích số từ chuỗi. Mỗi phương pháp phù hợp với các nhu cầu khác nhau tùy theo vị trí của số trong chuỗi. Nếu bạn đang vội, hãy chuyển đến phần giải quyết nhu cầu cấp bách nhất của bạn.

Trích số từ đầu chuỗi trong Excel

Đôi khi, bạn sẽ gặp tình huống số nằm ở đầu chuỗi. Trong các trường hợp như vậy, bạn sẽ dùng LEFT() và FIND() để trích số từ chuỗi. Khi bạn kết hợp các hàm thành một công thức, các số được trích theo logic sau:

• FIND(): Tìm vị trí của chữ cái đầu tiên trong chuỗi.

• LEFT(): Trích các ký tự từ đầu chuỗi đến vị trí chữ cái đầu tiên trừ một.

Phần đầu trong công thức dưới đây tìm chữ cái đầu tiên trong chuỗi. Sau đó, phần thứ hai trích mọi thứ trước chữ cái đó, chỉ để lại các số ở đầu. Nếu không có chữ cái nào, nó trả về toàn bộ chuỗi.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Trích số từ đầu chuỗi trong Excel. Ảnh: Tác giả.

Phương pháp này phù hợp khi làm việc với tập dữ liệu thường dùng tiền tố số như số đơn hàng, mã thành viên và số lô.

Trích số từ cuối chuỗi trong Excel

Nếu bạn muốn trích số từ cuối chuỗi trong Excel, bạn sẽ cần dùng các hàm RIGHT(), LEN() và FIND(). Giả sử bạn có dữ liệu sau trong trang tính Excel. Bạn sẽ kết hợp các hàm để trích số theo đó:

• FIND(): Tìm vị trí của chữ số đầu tiên trong chuỗi.

• LEN(): Tính số ký tự từ chữ số đầu tiên đến cuối chuỗi.

• RIGHT(): Trích các ký tự từ chữ số đầu tiên đến cuối chuỗi.

Công thức dưới đây loại bỏ toàn bộ phần văn bản đứng trước và trả về số đầu tiên cùng mọi thứ theo sau trong chuỗi.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Trích số từ cuối chuỗi trong Excel. Ảnh: Tác giả.

Phương pháp này hữu ích nhất với các tập dữ liệu có ký tự chữ-số nhất quán, như SKU sản phẩm, ID đơn hàng và số sê-ri.

Trích số ở bất kỳ vị trí nào trong chuỗi trong Excel

Nếu bạn có tập dữ liệu mà số có thể xuất hiện ở bất kỳ đâu trong chuỗi, bạn sẽ cần các công thức nâng cao dùng TEXTJOIN(), MID() và ROW() để trích số.

Kết hợp các hàm này thành một công thức sẽ trích số từ chuỗi theo logic sau: 

• ROW() và MID(): Lặp qua từng ký tự trong chuỗi để xác định liệu nó có phải là số hay không.

• IFERROR(): Kiểm tra logic để tách riêng các số đã nhận diện khỏi các ký tự khác.

• TEXTJOIN(): Kết hợp tất cả các ký tự số đã trích thành một chuỗi số duy nhất.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Trích số ở bất kỳ vị trí nào trong chuỗi. Ảnh: Tác giả.

Phương pháp này phù hợp nhất khi tập dữ liệu của bạn có ký tự chữ-số với số xuất hiện ở bất cứ đâu trong chuỗi. Lưu ý, không phải tất cả các hàm này đều có trong các phiên bản Excel cũ. Hãy xem lộ trình kỹ năng Những điều cơ bản về Excel để tìm hiểu thêm về các hàm và công thức có trong các phiên bản Excel mới nhất.

Trích số bằng cách lọc các ký tự không mong muốn

Đôi khi, dữ liệu của bạn khá lộn xộn. May mắn là bạn có thể kết hợp các hàm SUBSTITUTE() và TEXTJOIN(). Chỉ cần làm theo các bước sau:

• SUBSTITUTE(): Hàm SUBSTITUTE() có thể dùng để thay thế tất cả các ký tự không phải số trong chuỗi bằng một dấu tách, như khoảng trắng hoặc chuỗi rỗng. Ví dụ, bạn có thể thay chữ cái và ký tự đặc biệt bằng khoảng trắng.

• TEXTJOIN(): Sau đó, hàm TEXTJOIN() có thể dùng để nối các ký tự còn lại (giờ chỉ còn số) thành một chuỗi duy nhất.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Trích số từ chuỗi bằng cách lọc ký tự. Ảnh: Tác giả.

Khi nào bạn có thể cần trích số từ chuỗi trong Excel

Giờ hãy xem hai ví dụ nhỏ để thực hành trích số từ chuỗi.

Ví dụ 1: Trích số từ mã sản phẩm

Giả sử bạn đang quản lý tồn kho sản phẩm, mỗi mặt hàng được nhận diện bằng một mã sản phẩm duy nhất. Các mã này là chuỗi chữ-số như "PROD1234", "ITEM5678" hoặc "SKU91011."

Vì vậy, để trích Mã sản phẩm dạng số từ các mã chữ-số, chúng ta dùng các hàm dựa trên vị trí của số. Hãy nhớ, nếu số nằm ở cuối, áp dụng RIGHT(), LEN() và FIND(). Nếu xuất hiện ở đầu, dùng LEFT() và FIND().

Ví dụ dưới đây trích phần số của mã sản phẩm từ phía bên phải mã.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Trích số từ mã sản phẩm trong Excel. Ảnh: Tác giả.

Ví dụ 2: Dữ liệu khảo sát có nhúng số

Dữ liệu khảo sát đôi khi chứa cả chữ và số như "Đánh giá 5 trên 10", "Điểm: 8" hoặc "3 sao". Chúng ta sẽ phải xử lý một chút trước khi có thể tính trung bình hoặc xác định xu hướng.

Vì số có thể xuất hiện ở bất kỳ đâu trong văn bản, cần một công thức động dùng TEXTJOIN(), MID() và ROW().

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Trích số ở bất kỳ vị trí nào trong Excel. Ảnh: Tác giả.

Sao chép và dán các ví dụ mã cho các trường hợp đặc biệt

Ở phần cuối, tôi muốn cung cấp cho bạn một số công thức bổ sung xử lý các kịch bản khác nhau và cụ thể. Cứ thoải mái sao chép và dán, hy vọng chúng sẽ giải quyết vấn đề cụ thể bạn đang gặp. 

Trích khối số liên tiếp đầu tiên

Nếu chuỗi của bạn chứa nhiều dãy số nhưng bạn chỉ cần nhóm đầu tiên, hãy thử giải pháp này dùng các hàm LET() và SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Trích khối liên tiếp đầu tiên từ chuỗi trong Excel. Ảnh: Tác giả.

Trích số nằm trong ngoặc đơn

Trong các trường hợp số xuất hiện bên trong ngoặc đơn, hãy dùng:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Trích số nằm trong ngoặc đơn trong Excel. Ảnh: Tác giả.

Trích số sau một ký tự cụ thể

Khi dữ liệu của bạn dùng dấu hai chấm làm dấu tách (ví dụ: "Điểm: 8"), hãy lấy số bằng:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Trích số sau một ký tự đặc biệt trong Excel. Ảnh: Tác giả.

Công thức này lấy mọi thứ sau dấu hai chấm. Bọc kết quả với VALUE() nếu bạn cần giá trị số. Thay dấu hai chấm nếu bạn có dấu tách khác.

Trích tất cả các số và gộp thành một giá trị

Để loại bỏ mọi ký tự không phải chữ số và gộp tất cả ký tự số trong chuỗi, dùng công thức này:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Trích tất cả các số và gộp chúng thành một giá trị trong Excel. Ảnh: Tác giả.

Mẹo: Trong các phiên bản Excel cũ, hãy nhập công thức mảng (Ctrl+Shift+Enter).

Trích số bao gồm cả phần thập phân

Với các chuỗi số có thể bao gồm dấu thập phân (ví dụ, "Giá: 45.99"), hãy dùng công thức này để giữ cả chữ số và dấu chấm thập phân:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Trích số bao gồm cả dấu thập phân trong Excel. Ảnh: Tác giả.

Lưu ý: Công thức này giả định chỉ có một dấu thập phân.

Kết luận

Trích số từ chuỗi là một kỹ thuật quan trọng để làm sạch dữ liệu. Với tư cách là một nhà phân tích dữ liệu, tôi khuyến khích bạn học cách áp dụng các công thức khác nhau đã thảo luận trong bài viết này.

Nếu bạn muốn nâng cao kỹ năng Excel, tôi khuyến nghị tham gia các khóa Phân tích Dữ liệu trong Excel và Trực quan hóa Dữ liệu trong Excel để làm chủ phân tích và trình bày dữ liệu. Cũng hãy thử khóa  Lập mô hình Tài chính trong Excel có thể mở ra nhiều cơ hội.