Bạn có bao giờ gặp phải trường hợp muốn tách số ra khỏi chuỗi trong excel mà không biết cách làm như thế nào? Bạn có thể nghĩ rằng đây là một công việc khó khăn và tốn nhiều thời gian. Nhưng thực ra, có rất nhiều cách để bạn có thể tách số ra khỏi chuỗi trong excel một cách nhanh chóng và dễ dàng. Trong bài viết này, Unica sẽ giới thiệu cho bạn 6 cách và hàm tách số ra khỏi chuỗi trong excel chỉ trong 5 giây.
Tách số ra khỏi chuỗi trong excel để làm gì?
Tách số ra khỏi chuỗi trong Excel có thể giúp bạn thực hiện nhiều tác vụ khác nhau như là:
-
Phân tích dữ liệu: Khi bạn có một cột chứa thông tin kết hợp giữa chữ và số (ví dụ: "ABC123"), việc tách số ra khỏi chuỗi sẽ giúp bạn phân tích và sắp xếp dữ liệu dễ dàng hơn.
-
Tính toán và thống kê: Sau khi tách số ra, bạn có thể thực hiện các phép toán hoặc thống kê trên các giá trị số này mà không cần phải loại bỏ chữ cái.
-
Định dạng lại dữ liệu: Đôi khi, bạn cần định dạng lại dữ liệu để phù hợp với các yêu cầu hoặc chuẩn khác. Việc tách số ra khỏi chuỗi có thể là bước cần thiết để thực hiện điều này.
-
Xử lý dữ liệu nhập sai: Nếu dữ liệu được nhập sai hoặc không chuẩn mực (ví dụ: "12abc34"), việc tách số ra khỏi chuỗi có thể giúp bạn chỉ lấy các giá trị số.
-
Tạo các công thức phức tạp: Bằng cách tách số ra khỏi chuỗi, bạn có thể sử dụng kết quả trong các công thức phức tạp hơn để thực hiện các tính toán phức tạp hơn.
Tách số ra khỏi chuỗi trong Excel có thể giúp bạn thực hiện nhiều tác vụ khác nhau
Có nhiều cách để thực hiện việc tách số ra khỏi chuỗi trong Excel, bao gồm sử dụng các công cụ như các hàm như LEFT, MID, RIGHT hoặc sử dụng các công cụ trong tab "Dữ liệu" như "Text to Columns". Cách thực hiện cụ thể sẽ được chúng tôi giới thiệu ở mục tiếp theo.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
Hàm tách số ra khỏi chuỗi trong excel
Có rất nhiều cách để tách số ra khỏi chuỗi trong Excel như là sử dụng Flash Fill, sử dụng công thức, dùng hàm lấy số từ chuỗi trong excel left, hàm right hoặc hàm mid. Ngoài ra, bạn cũng có thể dùng hàm search để tách số ra khỏi chuỗi.
Tách chữ và số ra bằng cách sử dụng Flash Fill
Flash Fill là một tính năng thông minh của excel, cho phép bạn tự động điền dữ liệu theo một mẫu nhất định mà không cần sử dụng công thức tách số trong excel hay macro. Trên thực tế, bạn hoàn toàn có thể dùng Flash Fill để tách ký tự trong một câu.
Ví dụ: Cho bảng dữ liệu 1:
Cho bảng dữ liệu
Yêu cầu: Dùng Flash Fill để tách họ, tên đệm và tên của từng học sinh trong bảng 1.
Cách tách số trong excel:
Bước 1: Nhập tay họ, tên đệm và tên của học sinh Nguyễn Minh Anh vào từng ô B3, C3 và D3.
Nhập tay họ, tên đệm và tên của học sinh Nguyễn Minh Anh
Bước 2: Đặt con trỏ chuột vào ô B3.
Bước 3: Trên thanh công cụ, bạn chọn thẻ Data, chọn tiếp Flash Fill trong Data Tools.
Chọn tiếp Flash Fill trong Data Tools
Excel sẽ thu được kết quả như sau:
Kết quả thu được
Bước 4: Làm tương tự với cột tên đệm và tên sẽ thu được bảng kết quả như sau:
Kết quả cột tên đệm và tên
Cách tách chữ trong Excel bằng hàm LEFT
LEFT là một hàm lọc lấy số trong excel cho phép bạn trích xuất một số ký tự bắt đầu từ bên trái của một chuỗi. Bạn có thể sử dụng hàm LEFT để tách chữ ra khỏi chuỗi trong excel, nếu bạn biết chính xác số ký tự chữ mà bạn muốn tách ra.
Công thức sử dụng hàm tách số ra khỏi chuỗi trong excel LEFT có dạng như sau:
=LEFT(chuỗi,số_ký_tự)
Trong công thức trên:
- chuỗi: là ô chứa chuỗi mà bạn muốn tách chữ ra.
- số_ký_tự: là số ký tự chữ mà bạn muốn tách ra từ bên trái của chuỗi.
Ví dụ: Cho bảng dữ liệu 2:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm excel lấy số trong chuỗi LEFT để tách toàn bộ số trong mã hàng hóa ở bảng 2.
Cách tách số và chữ ra khỏi chuỗi trong excel:
Bước 1: Nhập công thức hàm tách số ra khỏi chuỗi trong excel:
=LEFT(B21;3) vào ô C21.
Nhập công thức LEFT
Bước 2: Nhấn Enter, kết quả thu được là 001.
Kết quả sau nhập công thức
Bước 3: Nhập công thức tương tự xuống các ô bên dưới, bạn sẽ thu được bảng kết quả cuối cùng như sau:
Kết quả thu được
Lưu ý: Hàm LEFT chỉ hoạt động khi bạn biết trước số ký tự chữ mà bạn muốn tách ra. Nếu số ký tự chữ không cố định, hoặc nếu bạn không biết số ký tự chữ, bạn không nên sử dụng hàm LEFT. Thay vào đó, bạn có thể sử dụng các cách khác mà tôi sẽ giới thiệu tiếp theo.
Hàm tách số ra khỏi chuỗi trong excel - RIGHT
Hàm RIGHT là một hàm văn bản trong excel, cho phép bạn trích xuất một số ký tự kết thúc từ bên phải của một chuỗi. Bạn có thể sử dụng hàm RIGHT để tách chữ ra khỏi chuỗi trong excel, nếu bạn biết chính xác số ký tự chữ mà bạn muốn tách ra.
Công thức sử dụng hàm RIGHT có dạng như sau:
=RIGHT(chuỗi,số_ký_tự)
Trong công thức trên:
- chuỗi: là ô chứa chuỗi mà bạn muốn tách chữ ra.
- số_ký_tự: là số ký tự chữ mà bạn muốn tách ra từ bên phải của chuỗi.
Ví dụ: Cho bảng dữ liệu 3:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm RIGHT để tách toàn bộ phần số trong mã sản phẩm ở bảng 3.
Cách tách số và chữ trong excel:
Bước 1: Đặt con trỏ chuột vào ô C36, nhập công thức hàm tách số ra khỏi chữ trong excel =RIGHT(B36;3).
Nhập công thức hàm RIGHT
Bước 2: Nhấn Enter, kết quả excel trả về là 001.
Kết quả sau khi nhập công thức
Bước 3: Kéo thả chuột xuống bên dưới, bảng kết quả bạn thu được sẽ là:
Kéo thả chuột xuống bên dưới
Lưu ý: Hàm RIGHT cũng chỉ hoạt động khi bạn biết trước số ký tự chữ mà bạn muốn tách ra. Nếu số ký tự chữ không cố định, hoặc nếu bạn không biết số ký tự chữ, bạn không nên sử dụng hàm RIGHT. Thay vào đó, bạn có thể sử dụng các cách khác mà tôi sẽ giới thiệu tiếp theo.
Sử dụng MID đơn giản
Hàm MID là một hàm văn bản trong excel, cho phép bạn trích xuất một số ký tự bắt đầu từ một vị trí xác định trong một chuỗi. Bạn có thể sử dụng hàm MID để tách số ra khỏi chuỗi trong excel, nếu bạn biết chính xác vị trí và số ký tự số mà bạn muốn tách ra.
Công thức sử dụng hàm tách số ra khỏi chuỗi trong excel MID có dạng như sau:
=MID(chuỗi,vị_trí,số_ký_tự)
Trong công thức trên:
- chuỗi: là ô chứa chuỗi mà bạn muốn tách số ra.
- vị_trí: là vị trí bắt đầu của số mà bạn muốn tách ra trong chuỗi.
- số_ký_tự: là số ký tự số mà bạn muốn tách ra.
Ví dụ: Cho bảng dữ liệu 4:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm MID để tách toàn bộ 4 ký tự đằng sau MH của mã hàng hóa trong bảng 4.
Cách lấy số trong chuỗi excel:
Bước 1: Đặt công thức hàm tách số và chữ trong excel =MID(B52;3;4) vào ô C52.
Nhập công thức MID
Bước 2: Nhấn Enter, Excel sẽ trả về kết quả là A001.
Excel sẽ trả về kết quả là A001
Bước 3: Copy công thức xuống bên dưới, bảng kết quả cuối bạn nhận được là:
Copy công thức xuống bên dưới
Lưu ý: Hàm MID cũng chỉ hoạt động khi bạn biết trước vị trí và số ký tự số mà bạn muốn tách ra. Nếu vị trí và số ký tự số không cố định, hoặc nếu bạn không biết vị trí và số ký tự số, bạn không nên sử dụng hàm MID. Thay vào đó, bạn có thể sử dụng các cách khác mà tôi sẽ giới thiệu tiếp theo.
Cách tách dãy số trong excel bằng hàm SEARCH
Hàm SEARCH là một hàm văn bản trong excel, cho phép bạn tìm kiếm một ký tự hoặc một chuỗi con trong một chuỗi lớn, và trả về vị trí của ký tự hoặc chuỗi con đó. Bạn có thể sử dụng hàm SEARCH để tìm ra vị trí của số trong chuỗi, và sau đó sử dụng hàm LEFT, RIGHT, hoặc MID để tách số ra khỏi chuỗi.
Công thức sử dụng hàm tách số ra khỏi chuỗi trong excel SEARCH có dạng như sau:
=SEARCH(ký_tự_hoặc_chuỗi_con,chuỗi,[vị_trí_bắt_đầu])
Trong công thức trên, ký_tự_hoặc_chuỗi_con là ký tự hoặc chuỗi con mà bạn muốn tìm kiếm trong chuỗi, chuỗi là ô chứa chuỗi mà bạn muốn tìm kiếm, và vị_trí_bắt_đầu là vị trí bắt đầu tìm kiếm trong chuỗi. Tham số này là tùy chọn, nếu bạn không nhập thì mặc định là 1.
Ví dụ: Cho bảng dữ liệu 5:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm excel tách số và chữ SEARCH kết hợp cùng hàm LEFT để tìm các ký tự đằng trước chữ t trong bảng dữ liệu 5.
Cách tách số ra khỏi chuỗi ký tự:
Bước 1: Ở ô B3, bạn nhập công thức hàm lấy số trong excel =LEFT(A3;SEARCH("t";A3)-1).
Nhập công thức hàm SEARCH kết hợp cùng hàm LEFT
Bước 2: Nhấn Enter, Excel sẽ trả về kết quả là Khóa.
Excel sẽ trả về kết quả là Khóa
Bước 3: Sao chép công thức xuống dưới bằng cách kéo thả chuột, kết quả thu được như sau:
Kết quả thu được
Lưu ý: Hàm SEARCH không phân biệt chữ hoa chữ thường, nên nếu bạn muốn tìm kiếm một ký tự hoặc chuỗi con cụ thể, bạn nên sử dụng hàm FIND thay thế. Hàm FIND có cùng cú pháp với hàm SEARCH, nhưng nó phân biệt chữ hoa chữ thường. Ví dụ, nếu bạn muốn tìm vị trí của ký tự “A” trong chuỗi “ABC250”, bạn có thể sử dụng công thức sau:
=FIND("A",A2)
Công thức này sẽ trả về giá trị 1, là vị trí của ký tự “A” trong chuỗi. Nếu bạn sử dụng hàm SEARCH thay vì hàm FIND, bạn sẽ nhận được cùng kết quả. Nhưng nếu bạn muốn tìm vị trí của ký tự “a” trong chuỗi “ABC250”, bạn sẽ nhận được lỗi #VALUE! nếu bạn sử dụng hàm FIND, và giá trị 1 nếu bạn sử dụng hàm SEARCH.
Hàm tách số ra khỏi chuỗi trong Excel: Find
Ví dụ: Cho bảng dữ liệu sau, yêu cầu tách mã số sinh viên và tên sinh viên thành những dữ liệu riêng biệt.
Bước 1: Áp dụng công thức hàm tách số trong dãy số:
=FIND({0;1;2;3;4;5;6;7;8;9};C4). Hàm này chúng ra sẽ tìm từ số 0 - 9 trong chuỗi C4.
Tách số ra khỏi chuỗi trong Excel
Bước 2: Kéo thả chuột để hiển thị kết quả ở các ô phía sau.
Kéo thả chuột để hiển thị kết quả
Bước 3: Đối với những ô tính hiện lỗi VALUE, bạn có thể nhấn đúp chuột vào ô đầu tiên và thêm &"0123456789" vào cuối hàm. Thao tác này giúp bạn nối thêm chuỗi từ 0-9. Như vậy, bạn sẽ có công thức như sau: =FIND({1;2;3;4;5;6;7;8;9};C4&"0123456789")
Công thức Find
Bước 4: Thêm hàm Min ở phía ngoài và nhấn Enter. Lúc này kết quả trả về là vị trí tìm thấy và là vị trí nhỏ nhất. Tức là trong chuỗi C4 từ 0-9 để tìm được vị trí đầu tiên và trả về.
Kết quả trả về là vị trí tìm thấy và là vị trí nhỏ nhất
Bước 5: Để xem cách chạy của hàm này, bạn chọn vào ô vừa tìm vị trí số và nhấn chọn vào Formulas -> chọn Evaluate Arrows.
Thao tác xem cách chạy của hàm
Tách số ra bằng cách sử dụng VBA
VBA là một ngôn ngữ lập trình cho phép bạn tạo các macro và hàm tùy chỉnh trong excel. Bạn có thể sử dụng VBA để tách số ra khỏi chuỗi trong excel, nếu bạn có kiến thức về lập trình và biết cách sử dụng VBA Editor.
Để sử dụng VBA để tách số ra khỏi chuỗi trong excel, bạn có thể làm theo các bước sau:
Bước 1: Mở VBA Editor bằng cách nhấn tổ hợp phím Alt + F11.
Bước 2: Chọn Insert > Module để tạo một module mới.
Tạo một module mới
Bước 3: Nhập vào module đó đoạn code sau:
Nhập vào module đoạn code
Nhập vào module đoạn code
Đoạn code trên sẽ tạo một hàm tùy chỉnh có tên là ExtractNumber, nhận vào một chuỗi và trả về số được tách ra từ chuỗi đó. Hàm này hoạt động bằng cách duyệt qua từng ký tự trong chuỗi, và nếu ký tự đó là số, thì nối nó vào một biến tạm thời. Sau khi duyệt xong, hàm này sẽ chuyển biến tạm thời thành kiểu số và trả về kết quả.
Để chạy đoạn code trên, bạn nhấn phím Run trên thanh công cụ hoặc bấm phím F5.
Nhấn phím Run để chạy code
Sau khi chạy xong thì thoát khỏi giao diện VBA.
Ở cột Emp Department, ô đầu tiên bạn nhập cú pháp =GetText(A2).
Ở đây, hàm tách lấy số trong excel GetText có được do mình tạo hàm từ Code VBA.
Nhập hàm GetText
Tương tự, để hiển thị cột Emloyee No, tại ô đầu tiên bạn gõ cú pháp hàm =GetNumber(A2).
Nhập công thức hàm GetNumber
Hàm lọc số trong chuỗi GetNumber có được do mình tạo hàm từ Code VBA.
Tách số ra bằng cách sử dụng Power Query
Power Query là một công cụ cho phép bạn nhập, biến đổi, và phân tích dữ liệu từ nhiều nguồn khác nhau trong excel. Bạn có thể sử dụng Power Query để tách số ra khỏi chuỗi trong excel, nếu bạn muốn làm việc với dữ liệu lớn và phức tạp.
Ví dụ: Cho bảng dữ liệu như sau:
Cho bảng dữ liệu
Yêu cầu: Tách chữ và số trong bảng dữ liệu trên ra 2 cột riêng biệt bằng cách dùng Power Query.
Cách làm:
Bước 1: Bôi đen toàn bộ phần dữ liệu từ A1-A13. Trên thanh công cụ, chọn Insert > chọn Table và nhấn OK để hoàn tất việc tạo bảng.
Chọn Table và nhấn OK để hoàn tất việc tạo bảng
Bước 2: Click chuột vào thẻ Data > Chọn From Table/Range.
Thao tác này sẽ giúp hiện thị giao diện của Power Query. Chọn vào tiêu đề để bôi đen toàn bộ vùng dữ liệu cần tách.
Bước 3: Chọn thẻ Home > Chọn Split Column > Chọn By non Digit to Digit.
Chọn By non Digit to Digit
Kết quả là dữ liệu của bạn sẽ được tách như sau:
Kết quả là dữ liệu của bạn
Bước 4: Để tải dữ liệu về file Excel, bạn vào thẻ Home rồi chọn Close & Load to…
Close & Load to…
Bước 5: Trong bảng Import Data, bạn chọn Existing worksheet và chọn vào vị trí mà bạn muốn hiển thị kết quả. Ở đây, tôi sẽ chọn ô B1.
Chọn vào vị trí mà bạn muốn hiển thị kết quả
Bước 6: Nhấn OK, Excel sẽ trả về kết quả như sau:
Kết quả cuối cùng
Kết luận
Như vậy, Unica đã giới thiệu cho bạn 6 cách và hàm tách số ra khỏi chuỗi trong excel. Bạn có thể chọn cách nào phù hợp với nhu cầu và khả năng của bạn. Hy vọng bài viết này sẽ hữu ích cho bạn trong việc làm việc với dữ liệu trong excel.
