Trang chủ Tin học văn phòng Hàm tách số ra khỏi chuỗi trong excel cực dễ chỉ 5 giây

Hàm tách số ra khỏi chuỗi trong excel cực dễ chỉ 5 giây

16-04-2024

6320 Lượt xem

Mục lục

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"

ĐĂNG KÝ MUA NGAY

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.

1. 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:

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:

- 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:

2. 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:

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:

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.

3. 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:

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à:

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.

4. 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:

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à:

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.

5. 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:

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:

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.

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