Hàm lấy ký tự có điều kiện trong Excel là gì? Cách sử dụng

Khi chúng ta thực hiện xử lý một bảng tính có định dạng là text hoặc number thì việc xử lý, phân tách một hay nhiều ký tự khác nhau chính là điều mà chúng ta thường xuyên phải thực hiện. Excel là một phần mềm hỗ trợ rất tốt cho công việc này. Đọc ngay bài viết sau để tìm hiểu về cách dùng hàm lấy ký tự có điều kiện trong Excel, cách lấy ký tự sau dấu phẩy, trước sau và giữa hai ký tự đặc biệt. 

Hàm lấy ký tự bất kỳ trong chuỗi Excel

Chúng ta có rất nhiều hàm lấy ký tự có điều kiện trong Excel, tiêu biểu trong đó chính là hàm MID, LEFT, RIGHT. Hàm lấy ký tự bất kỳ trong chuỗi Excel không hề khó khăn trong việc thực hiện. Cách sử dụng như sau: 

• Hàm MID:

Công thức thực hiện: =MID(text, start_num, num_chars)

Trong đó:

Num_chars: Số ký tự cần cắt ra từ chuỗi ký tự.

Start_num: Vị trí của ký tự đầu tiên cần tách trong text.

Text: Chuỗi văn bản cần trích xuất.

Hàm lấy ký tự bất kỳ trong chuỗi Excel

• Hàm LEFT: 

Công thức thực hiện: =LEFT(text, [num_chars])

Trong đó:

[num_chars]: Số ký tự cần cắt chuỗi, nếu không được nhập sẽ mặc định bằng 1.

Text: Chuỗi văn bản cần trích xuất các ký tự từ bên trái, có thể là tham chiếu ô, văn bản.

• Hàm RIGHT: 

Công thức thực hiện: =RIGHT(text, [num_chars])

Trong đó:

[num_chars]: Số ký tự cần cắt chuỗi, nếu không được nhập sẽ mặc định bằng 1.

Text: Chuỗi văn bản cần trích xuất các ký tự từ bên trái, có thể là tham chiếu ô, văn bản.

Lưu ý khi dùng hàm trích xuất ký tự trong Excel

Như chúng ta đã biết, hàm MID, RIGHT, LEFT chính là một trong những hàm thường xuyên được dùng để lấy ký tự có điều kiện trong Excel. Để sử dụng hàm trích xuất ký tự trong Excel được thuận tiện và nhanh chóng hơn, chúng ta cần quan tâm tới các yếu tố sau: 

• Hàm MID trong Excel: =MID(text, start_num, [num_chars]).

– Nếu start_num lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là chuỗi rỗng.

– num_chars phải là số nguyên dương.

– num_chars: số ký tự của chuỗi con cần trích ra.

– start_num: vị trí bắt đầu trích ra chuỗi con, tính từ bên trái sang.

– text: chuỗi văn bản cần trích xuất.

Lưu ý khi dùng hàm trích xuất ký tự trong Excel

• Hàm RIGHT Trong Excel:=RIGHT(text, [num_chars]).

– Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text.

– num_chars phải là số nguyên dương.

– num_chars là số ký tự cần trích ra phía bên phải của chuỗi text, mặc định là 1.

– text chính là chuỗi văn bản cần trích xuất ký tự.

• Hàm LEFT Trong Excel: = LEFT(text, [num_chars]).

– Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text.

– num_chars phải là số nguyên dương.

– num_chars là số ký tự cần trích ra phía bên trái chuỗi text, mặc định là 1.

– text là chuỗi văn bản cần trích xuất ký tự.

Hàm tách số ra khỏi chuỗi trong Excel

Hàm tách số ra khỏi chuỗi trong Excel chính là hàm RIGHT kết hợp với hàm LEN và hàm MIN. Công thức hàm như sau: 

=RIGHT(chuỗi bạn cần tách; LEN(chuỗi bạn cần tách)-Vị trí bắt đầu số + 1) và enter.

Ví dụ: 

=RIGHT(C4; LEN(C4)+ 1-MIN(FIND({0;1;2;3;4;5;6;7;8;9}; C4&”0123456789″)))

=RIGHT(chuỗi cần tách; LEN(chuỗi cần tách); +1-MIN(FIND({0;2;3;5;6;7;8;9},chuỗi cần tách & “02356789”))).

Hàm tách số ra khỏi chuỗi trong Excel

Ngoài ra, chúng ta hoàn toàn có thể sử tách số ra khỏi chuỗi bằng code VBA:

Bước 1: Kích chuột vào ô dữ liệu muốn tách chuỗi, nhấn tổ hợp phím Alt + F11 để mở cửa sổ làm việc của Microsoft Visual Basic for Applications. Nhấn chọn mục Insert trên thanh công cụ và tìm mới mục Module.

Bước 2: Copy đoạn code dưới đây và dán vào ô nhập code như sau: 

• Function ExtractNumber
  Dim iCount Long
• Dim sText As String
• Text = Cell
• For lCount = Len(sText) To 1 Step -1
• If IsNumeric(Mid(sText, lCount, 1)) Then
• lNum = Mid(sText, Count, 1) & iNum
• End If

Next Count

• ExtractNumber = CLng(lNum)
• End Function

Bước 3: Nhấn chuột phải vào cửa sổ Module và dán đoạn code trên vào. 

Bước 4: Ngay lúc này, hàm ExtractNumber đã tự động được thêm vào ô dữ liệu muốn tách chuỗi. Sao chép công thức xuống toàn bộ các ô còn lại.

Hàm lấy ký tự sau dấu phẩy trong Excel

Hàm lấy ký tự có điều kiện trong Excel giúp chúng ta có thể lấy được ký tự sau dấu phẩy một cách dễ dàng. Ngoài ra, chúng ta hoàn toàn có thể tách chuỗi bằng dấu phân cách, dấu gạch ngang, dấu gạch chéo, dấu hai chấm, dấu phẩy. Hàm lấy ký tự sau dấu phẩy trong Excel thường được dùng là FIND và SEARCH. 

=FIND(“ký tự hoặc cụm ký tự cần tìm kiếm”;ô tham số gốc để tìm kiếm;bắt đầu tìm kiếm từ ký tự số bao nhiêu).

=SEARCH(“ký tự hoặc cụm ký tự cần tìm kiếm”;ô tham số gốc để tìm kiếm;bắt đầu tìm kiếm từ ký tự số bao nhiêu).

Hàm lấy ký tự sau dấu phẩy trong Excel

Hàm tách chuỗi sau ký tự trong Excel

Hàm tách chuỗi sau ký tự trong Excel được dân văn phòng sử dụng thường xuyên chính là hàm RIGHT. Cú pháp thực hiện bao gồm: =RIGHT(text,[num_chars]). 

Trong đó:

• num_chars là số lượng ký tự mà các bạn muốn hàm RIGHT trích xuất.
• text là chuỗi văn bản các bạn muốn trích xuất các ký tự.

Lưu ý:

• Nếu num_chars lớn hơn độ dài của chuỗi văn bản thì hàm RIGHT trả về toàn bộ chuỗi văn bản.
• Num_chars luôn phải lớn hơn hoặc bằng 0.
• Nếu num_chars bỏ qua thì mặc định của nó là 1.

Hàm tách chuỗi sau ký tự trong Excel

Lấy ký tự giữa 2 ký tự đặc biệt

Để lấy ký tự giữa 2 ký tự đặc biệt, chúng ta sử dụng hàm LEN kết hợp với hàm SUBSTITUTE.

Công thức thực hiện như sau: =LEN(ô)-LEN(SUBSTITUTE(ô, ký tự,””))

Lưu ý. Hàm SUBSTITUTE của Excel là một hàm phân biệt dạng chữ, và vì thế các công thức trên đều phân biệt dạng chữ theo.

Lấy ký tự giữa 2 ký tự đặc biệt

Lỗi khi sử dụng hàm lấy ký tự đầu trong Excel

Các lối khi sử dụng hàm lấy ký tự đầu trong Excel nói riêng và hàm lấy ký tự có điều kiện trong Excel nói chung đều là vấn đề khiến người sử dụng Excel phải đau đầu. Các lỗi thường gặp và cách khắc phục bao gồm: 

1. #NAME?: Sai tên.

– Nguyên nhân:

+ Không có dấu 2 chấm trong mảng dữ liệu tham chiếu.

+ Nhập một chuỗi công thức mà không đặt trong dấu nháy đôi.

+ Dùng những hàm không thường trực trong Excel.

+ Dùng những ký tự không được phép trong công thức.

+ Nhập sai tên hàm. Ví dụ: ì thay vì if.

2. #DIV/0: Lỗi chia cho 0.

– Nguyên nhân:

+ Số chia trong công thức tham chiếu đến một ô trống.

+ Nhập vào công thức với số chia là 0.

Lỗi khi sử dụng hàm lấy ký tự đầu trong Excel

3. #N/A: Lỗi dữ liệu.

– Nguyên nhân:

+ Dùng một hàm tự tạo không hợp lý.

+ Quên nhiều hoặc một đối số trong các hàm tự tạo.

+ Giá trị trả về không tương thích với các hàm dò tìm.

Cách khắc phục: Chỉnh sửa ô mà hàm tham chiếu tới.

4. #REF!: Sai vùng tham chiếu.

– Nguyên nhân:

+ Tham chiếu, liên kết đến một ứng dụng không thực thi được.

+ Ô tham chiếu của công thức bị xóa.

5. #NUM!: Lỗi dữ liệu kiểu số. 

– Nguyên nhân:

+ Dùng một đối số không phù hợp trong công thức sử dụng.

+ Dùng một hàm trả về một giá trị quá nhỏ so hoặc quá lớn so với khả năng có thể tính toán của Excel.

+ Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được giá trị trả về.

Cách khắc phục: Trường hợp này chúng ta nên rút gọn giá trị tính toán lại.

6. #NULL!: Lỗi dữ liệu rỗng.

– Nguyên nhân:

+ Dùng một mảng không có phân cách.

+ Dùng một giá trị có định dạng không phù hợp.

7. #VALUE!: Lỗi giá trị.

– Nguyên nhân:

+ Thi hành một lệnh liên quan đến một hàm mà hàm đó lại cho giá trị lỗi.

+ Nhập quá nhiều tham số cho một hàm hoặc toán tử trong khi công thức của chúng chỉ cần một tham số.

+ Nhập vào công thức tính toán một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic.

+ Đang nhập hoặc chỉnh sửa một công thức nào đó nhưng chưa hoàn thành mà nhấn Enter.

1. #####: Lỗi độ rộng ô.

– Nguyên nhân:

+ Khi nhập số âm cho các giá trị ngày tháng hoặc thời gian.

+ Khi ô dữ liệu thiếu độ rộng.

Trên đây là về cách dùng hàm lấy ký tự có điều kiện trong Excel, cách lấy ký tự sau dấu phẩy, trước sau và giữa hai ký tự đặc biệt. Hy vọng với những thủ thuật trên đây sẽ giúp rút ngắn được thời gian khi sử dụng Excel này. Chúc các bạn thành công!

Xem thêm: Cách sử dụng hàm làm tròn lên 0.5 trong Excel nhanh chóng

• Cách sử dụng hàm làm tròn lên 0.5 trong Excel nhanh chóng

• Cách viết dấu nhỏ hơn hoặc bằng trong Excel và Word nhanh chóng

• Công thức tính điểm trung bình trong Excel đơn giản

• Cách sắp xếp tên giống nhau trong Excel đơn giản, nhanh chóng

• Cách tính tổng trong Excel nhanh nhất có kèm ví dụ chi tiết

• Trong bảng tính Excel hàm TODAY trả về kết quả như thế nào?

• Microsoft Excel là phần mềm gì? Tầm quan trọng của việc sử dụng Excel?