[Excel Tutorial] Hướng dẫn các hàm xử lý chuỗi trong Excel

Trong quá trình làm việc với bảng tính, việc xử lý dữ liệu văn bản là công việc diễn ra thường nhật của dân văn phòng, kế toán và các nhà phân tích dữ liệu. Việc thành thạo các hàm xử lý chuỗi trong Excel không chỉ giúp bạn dọn dẹp dữ liệu nhanh chóng mà còn tối ưu hóa thời gian xử lý các báo cáo phức tạp. Bài viết này, Starttrain sẽ chia sẻ chi tiết từ cú pháp, cách dùng đến các ví dụ thực tế cực kỳ trực quan của các hàm cắt chuỗi, ghép chuỗi thông dụng nhất hiện nay.

Tổng hợp các hàm xử lý chuỗi trong Excel cơ bản

Để bắt đầu làm chủ dữ liệu dạng văn bản, bạn cần nắm vững bộ 5 hàm cắt chuỗi kinh điển dưới đây.

Hàm LEFT

Hàm LEFT được sử dụng để cắt và lấy ra một số lượng ký tự nhất định từ phía bên trái (phần đầu) của một chuỗi văn bản.

Cú pháp: =left(text, [num_chars])
Chi tiết tham số:
- text (Bắt buộc): Chuỗi văn bản hoặc ô chứa chuỗi văn bản mà bạn muốn trích xuất.
- num_chars (Tùy chọn): Số lượng ký tự bạn muốn hàm left lấy ra. Nếu bỏ qua tham số này, Excel sẽ mặc định lấy 1 ký tự đầu tiên.
Lưu ý: num_chars phải là số lớn hơn hoặc bằng 0. Nếu num_chars lớn hơn tổng độ dài của chuỗi, hàm sẽ trả về toàn bộ chuỗi ban đầu.

Hàm RIGHT

Ngược lại với LEFT, hàm RIGHT được dùng để cắt và lấy ra số lượng ký tự mong muốn từ phía bên phải (phần cuối) của chuỗi văn bản.

Cú pháp: =right(text, [num_chars])
Chi tiết tham số:
- text (Bắt buộc): Chuỗi văn bản nguồn chứa ký tự cần lấy.
- num_chars (Tùy chọn): Số lượng ký tự cần trích xuất tính từ bên phải qua. Mặc định là 1 nếu bỏ qua.
Lưu ý: Tương tự hàm left, kết quả của hàm right luôn được trả về dưới dạng định dạng văn bản (text), kể cả khi kết quả đó là các chữ số.

Hàm MID

Hàm MID cực kỳ linh hoạt khi cho phép bạn cắt và trích xuất một chuỗi con từ bất kỳ vị trí nào ở giữa chuỗi văn bản gốc.

Cú pháp: =mid(text, start_num, num_chars)
Chi tiết tham số:
- text (Bắt buộc): Chuỗi văn bản gốc cần trích xuất dữ liệu.
- start_num (Bắt buộc): Vị trí của ký tự đầu tiên bạn muốn bắt đầu cắt (ký tự đầu tiên của chuỗi là vị trí số 1).
- num_chars (Bắt buộc): Số lượng ký tự bạn muốn lấy kể từ vị trí start_num.
Lưu ý: Nếu start_num lớn hơn độ dài của chuỗi văn bản gốc, hàm mid sẽ trả về một chuỗi rỗng (“”). Nếu start_num nhỏ hơn 1, hàm sẽ trả về lỗi #VALUE!.

Hàm TEXTBEFORE

Đây là một hàm xử lý chuỗi hiện đại (có sẵn trên phiên bản Excel 365 và Excel Web), dùng để lấy ra phần văn bản nằm trước một ký tự phân tách (delimiter) được chỉ định.

Cú pháp: =textbefore(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Chi tiết tham số:
- text (Bắt buộc): Văn bản cần tìm kiếm.
- delimiter (Bắt buộc): Ký tự phân tách dùng làm mốc để cắt (ví dụ: khoảng trắng, dấu phẩy, dấu gạch ngang).
- instance_num (Tùy chọn): Lần xuất hiện thứ mấy của ký tự phân tách mà bạn muốn cắt. Sử dụng số âm (ví dụ: -1) để tìm kiếm ngược từ cuối chuỗi lên.
Lưu ý: Hàm này giúp bạn tránh được việc lồng ghép phức tạp giữa các hàm left và find truyền thống.

Hàm TEXTAFTER

Tương tự như TEXTBEFORE, hàm TEXTAFTER được dùng để lấy ra phần văn bản nằm sau một ký tự phân tách được chỉ định.

Cú pháp: =textafter(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Chi tiết tham số:
- text (Bắt buộc): Văn bản cần tìm kiếm.
- delimiter (Bắt buộc): Ký tự phân tách dùng làm mốc.
- instance_num (Tùy chọn): Số lần xuất hiện của ký tự phân tách. Nếu điền -1, hàm sẽ xác định mốc cắt tại ký tự phân tách cuối cùng tính từ phải sang.
Lưu ý: Cực kỳ hữu ích trong việc tách tên ra khỏi họ và tên đệm trong danh sách tiếng Việt.

Tổng hợp 20+ hàm Excel nâng cao giúp giảm 50% thời gian làm báo cáo

Ví dụ minh họa các hàm xử lý chuỗi trong Excel

Để giúp bạn hình dung rõ nét nhất, chúng ta hãy cùng thực hành các hàm trên thông qua một bảng quản lý nhân viên thực tế dưới đây. Bảng dữ liệu gốc gồm các cột: STT, Mã NV, Tên đầy đủ. Mục tiêu của chúng ta là sử dụng công thức để tự động điền thông tin cho các cột còn lại.

Trích xuất mã phòng ban bằng hàm LEFT

Ta cần lấy ra 2 ký tự đầu tiên của Mã NV (cột B) để điền vào cột Mã phòng (cột D).

Tại ô D2, chúng ta nhập công thức: =LEFT(B2, 2)
Ô B2 chứa giá trị “KD-2021-001”. Hàm left lấy ra 2 ký tự đầu tiên từ bên trái, trả về kết quả là “KD”.

Xác định năm vào làm bằng hàm MID

Tiếp theo, ta cần trích xuất thông tin năm vào làm việc nằm ở giữa Mã NV (ví dụ: “2021” từ chuỗi “KD-2021-001”)

Tại ô E2, chúng ta nhập công thức: =MID(B2, 4, 4)
Bắt đầu từ vị trí thứ 4 (sau ký tự gạch ngang thứ nhất), hàm mid sẽ lấy ra đúng 4 ký tự tiếp theo. Kết quả thu được tại ô E2 là “2021”.

Lấy số thứ tự nhân viên bằng hàm RIGHT

Lần này, ta sẽ trích xuất 3 ký tự cuối cùng bên phải của Mã NV để xác định số thứ tự nhân viên.

Tại ô F2, chúng ta nhập công thức: =RIGHT(B2, 3)
Hàm right cắt từ bên phải qua 3 ký tự của chuỗi “KD-2021-001” tại ô B2 và trả về kết quả là số thứ tự dạng văn bản: “001”.

Tách họ và tên đệm bằng hàm TEXTBEFORE

Đối với việc xử lý cột Họ + Tên đệm (cột G) từ cột Tên đầy đủ (cột C), ta sẽ áp dụng hàm TEXTBEFORE.

Tại ô G2, chúng ta nhập công thức: =TEXTBEFORE(C2, ” “, -1)
Ô C2 chứa chuỗi tên “Nguyễn Văn An”. Tham số delimiter là khoảng trắng ” “, tham số instance_num là -1 (tức khoảng trắng cuối cùng từ phải sang, ngăn cách giữa chữ “Văn” và “An”). Hàm sẽ lấy toàn bộ văn bản đứng trước khoảng trắng cuối cùng này, trả về kết quả là “Nguyễn Văn”.

Tách tên chính bằng hàm TEXTAFTER

Để lấy riêng phần Tên chính (cột H) từ cột Tên đầy đủ, hàm TEXTAFTER là sự lựa chọn tối ưu nhất.

Tại ô H2, chúng ta nhập công thức: =TEXTAFTER(C2, ” “, -1)
Tương tự như trên, hàm định vị khoảng trắng cuối cùng và chỉ lấy phần văn bản đứng sau khoảng trắng đó. Kết quả nhận được tại ô H2 là “An”.

Sau khi thiết lập công thức cho dòng đầu tiên, chúng ta chỉ cần kéo chuột sao chép công thức xuống các dòng tiếp theo trong bảng. Toàn bộ bảng dữ liệu từ dòng 2 đến dòng 8 đã được xử lý tự động.

Chức năng dấu & trong xử lý chuỗi Excel

Bên cạnh việc cắt chuỗi bằng các hàm chuyên dụng, nhu cầu ghép nhiều chuỗi văn bản lại với nhau cũng cực kỳ phổ biến. Trong Excel, toán tử ghép chuỗi đơn giản và mạnh mẽ nhất chính là dấu và &.

Cách hoạt động: Dấu & giúp bạn nối các ô dữ liệu hoặc các đoạn văn bản tĩnh nằm trong dấu ngoặc kép lại với nhau thành một chuỗi duy nhất.
Ví dụ thực tế:
- Nếu bạn muốn ghép Họ + Tên đệm ở ô G2 (“Nguyễn Văn”) và Tên ở ô H2 (“An”) lại với nhau nhưng có thêm khoảng trắng ở giữa, công thức sẽ là: =G2 & ” ” & H2
- Kết quả trả về sẽ là chuỗi đầy đủ: “Nguyễn Văn An”.
- Bạn cũng có thể kết hợp văn bản tĩnh: =”Chào bạn ” & H2 -> Kết quả: “Chào bạn An”.

Một số hàm xử lý chuỗi trong Excel khác

Ngoài các hàm cốt lõi đã nêu ở trên, để giải quyết triệt để mọi tình huống dọn dẹp dữ liệu phức tạp trong thực tế, bạn cần làm quen thêm một số hàm xử lý chuỗi khác trong Excel như sau:

Hàm LEN

Hàm LEN dùng để đo lường độ dài của một chuỗi ký tự bằng cách đếm tất cả các ký tự có trong chuỗi đó, bao gồm cả chữ cái, chữ số, ký tự đặc biệt và mọi khoảng trắng.

Cú pháp: =LEN(text)
Cách sử dụng: Truyền trực tiếp văn bản hoặc tham chiếu đến ô cần đếm ký tự vào tham số text.
Ví dụ: =LEN(“Excel”) trả về kết quả là 5. =len(“A B”) trả về kết quả là 3 (tính cả khoảng trắng ở giữa).
Ứng dụng nâng cao: Hàm len thường được lồng ghép với các hàm left, right, mid để cắt chuỗi một cách linh hoạt khi các chuỗi có độ dài không đồng đều nhau.

Hàm TRIM

Hàm TRIM là trợ thủ đắc lực nhất trong quy trình làm sạch dữ liệu thô. Hàm này giúp bạn quét dọn và tự động loại bỏ tất cả các khoảng trắng thừa không cần thiết trong chuỗi văn bản.

Cú pháp: =TRIM(text)
Cách sử dụng: Bọc hàm trim bên ngoài ô dữ liệu bị lỗi khoảng trắng. Hàm sẽ loại bỏ toàn bộ khoảng cách ở đầu chuỗi, cuối chuỗi, và chỉ giữ lại duy nhất một khoảng trắng phân tách giữa các từ.
Ví dụ: =TRIM(” Nguyễn Văn An “) sẽ dọn dẹp sạch sẽ và trả về kết quả hoàn hảo là “Nguyễn Văn An”.
Lưu ý: Luôn sử dụng hàm trim trước khi thực hiện các hàm tìm kiếm như VLOOKUP hoặc MATCH để tránh các lỗi không tìm thấy dữ liệu do thừa dấu cách ẩn.

Hàm FIND

Hàm FIND được sử dụng để định vị chính xác vị trí bắt đầu của một ký tự hoặc một chuỗi con nằm bên trong một chuỗi văn bản lớn hơn.

Cú pháp: =FIND(find_text, within_text, [start_num])
Chi tiết tham số:
- find_text (Bắt buộc): Ký tự hoặc chuỗi con bạn cần tìm vị trí.
- within_text (Bắt buộc): Chuỗi văn bản lớn chứa đối tượng tìm kiếm.
- start_num (Tùy chọn): Vị trí ký tự bắt đầu tìm kiếm (mặc định là 1 từ đầu chuỗi).
Ví dụ: =FIND(“n”, “Nguyễn Văn An”) trả về vị trí thứ 6 (đây là chữ “n” đầu tiên xuất hiện trong từ “Nguyễn”).
Lưu ý cực kỳ quan trọng: Hàm find có phân biệt chữ hoa và chữ thường và không cho phép sử dụng các ký tự đại diện.

Hàm SEARCH

Tương tự như hàm FIND, hàm SEARCH dùng để tìm kiếm vị trí của ký tự hoặc chuỗi con. Tuy nhiên, hàm này sở hữu tính năng tìm kiếm rộng và linh hoạt hơn rất nhiều.

Cú pháp: =SEARCH(find_text, within_text, [start_num])
Sự khác biệt lớn: Hàm search không phân biệt chữ viết hoa hay viết thường. Bên cạnh đó, hàm này còn cho phép bạn sử dụng các ký tự đại diện như dấu chấm hỏi ? (thay thế cho 1 ký tự bất kỳ) và dấu sao * (thay thế cho một chuỗi ký tự bất kỳ).
Ví dụ: =SEARCH(“an”, “Nguyễn Văn An”) sẽ trả về kết quả là 8 (vị trí từ “Văn”) vì hàm không phân biệt chữ hoa hay thường, trong khi hàm find tìm kiếm chữ viết thường “an” sẽ báo lỗi hoặc bỏ qua từ “An” viết hoa.

Hàm CONCAT

Hàm CONCAT là phiên bản nâng cấp hoàn hảo của hàm concatenate truyền thống, dùng để liên kết, ghép nối nhiều chuỗi văn bản từ các ô hoặc các phạm vi dữ liệu lại với nhau.

Cú pháp: =CONCAT(text1, [text2],…)
Sự tiện lợi: Khác với toán tử & phải gõ từng ô một cách thủ công, hàm concat cho phép bạn truyền vào cả một vùng dữ liệu (ví dụ: A1:A10) để nối toàn bộ nội dung trong vùng đó lại thành một chuỗi duy nhất một cách cực kỳ nhanh chóng.
Ví dụ: =CONCAT(“Học”, ” “, “Excel”) trả về chuỗi “Học Excel”.

Hàm UPPER, LOWER, PROPER

Đây là bộ ba hàm xử lý định dạng viết hoa – viết thường cực kỳ hữu ích giúp chuẩn hóa tính đồng nhất và thẩm mỹ cho dữ liệu báo cáo của bạn.

Hàm UPPER: Chuyển đổi toàn bộ tất cả các chữ cái trong chuỗi thành chữ IN HOA.
- Cú pháp: =UPPEr(text)
- Ví dụ: =UPPER(“Nguyễn Văn An”) trả về “NGUYỄN VĂN AN”.
Hàm LOWER: Chuyển đổi toàn bộ tất cả các chữ cái trong chuỗi thành chữ viết thường.
- Cú pháp: =LOWER(text)
- Ví dụ: =LOWER(“NGUYỄN VĂN AN”) trả về “nguyễn văn an”.
Hàm PROPER: Chuẩn hóa viết hoa chữ cái đầu tiên của từng từ đơn lẻ, đồng thời chuyển toàn bộ các chữ cái còn lại về dạng viết thường. Hàm này sinh ra để chuyên xử lý danh sách họ và tên bị viết lộn xộn.
- Cú pháp: =PROPER(text)
- Ví dụ: =PROPER(“nGuYễN văN aN”) trả về kết quả cực kỳ chuẩn mực là “Nguyễn Văn An”.

Khuyến nghị khi làm việc với các hàm xử lý chuỗi trong Excel

Chú ý các khoảng trắng đầu/cuối

Một trong những nguyên nhân hàng đầu khiến các hàm tìm kiếm như VLOOKUP, MATCH hoặc phép so sánh bằng = trả về lỗi #N/A hoặc kết quả sai lệch là do sự tồn tại của các dấu cách thừa ẩn náu ở đầu hoặc cuối chuỗi dữ liệu thô.

Giải pháp xử lý: Hãy tạo thói quen bọc toàn bộ dữ liệu văn bản thô bằng hàm trim trước khi truyền vào các hàm cắt chuỗi hay tìm kiếm.

Với những nguồn dữ liệu tải về từ các phần mềm quản trị trực tuyến, ngoài khoảng trắng thông thường, chúng còn có thể chứa các ký tự xuống dòng hoặc ký tự không in được. Lúc này, hãy kết hợp hai hàm: =TRIM(CLEAN(A2)) để loại bỏ triệt để mọi ký tự rác hệ thống phát sinh.

Hiểu sự khác biệt giữa Text và Number

Một cái bẫy kinh điển mà hầu như người dùng Excel nào cũng từng vấp phải: Kết quả đầu ra của các hàm cắt chuỗi như LEFT, RIGHT, MID luôn luôn được định dạng dưới dạng Văn bản (Text), ngay cả khi kết quả trả về là một chuỗi các chữ số (ví dụ: cắt mã “001” từ chuỗi “KD-2021-001”).

Nếu bạn dùng kết quả “001” dạng văn bản này để thực hiện phép cộng trừ hoặc đem đi dò tìm (VLOOKUP) vào một bảng danh mục có định dạng số 1 (Number), Excel sẽ lập tức báo lỗi vì hai định dạng này hoàn toàn khác nhau.

Để chuyển đổi kết quả cắt chuỗi số về định dạng Số thực thụ, bạn có thể áp dụng 3 cách đơn giản sau:

Cách 1: Bọc ngoài bằng hàm value. Ví dụ: =VALUE(RIGHT(B2, 3)).
Cách 2: Thực hiện toán tử nhân với 1. Ví dụ: =RIGHT(B2, 3) * 1.
Cách 3: Cộng thêm số không. Ví dụ: =RIGHT(B2, 3) + 0.

Lưu ý về tính tương thích của các hàm xử lý chuỗi mới

Các hàm hiện đại cực kỳ tiện lợi như TEXTBEFORE, TEXTAFTER chỉ khả dụng trên phiên bản Microsoft Excel 365, Excel LTSC 2024 hoặc Excel Web.

Nếu bạn sử dụng các hàm này trong file báo cáo, sau đó gửi file cho đồng nghiệp hoặc đối tác đang sử dụng các phiên bản Excel cũ hơn (như Excel 2016, 2019, hay 2021 tiêu chuẩn), toàn bộ các ô công thức đó sẽ ngay lập tức bị lỗi hiển thị #NAME ?. Nếu file báo cáo của bạn được dùng để chia sẻ rộng rãi cho nhiều người cùng sử dụng, hãy ưu tiên dùng tổ hợp các hàm kinh điển như LEFT, RIGHT, MID phối hợp với FIND và LEN. Chúng tuy dài hơn một chút nhưng đảm bảo khả năng tương thích tuyệt đối trên mọi phiên bản Excel từ cổ chí kim.

Kết luận

Việc làm chủ các hàm xử lý chuỗi trong Excel không chỉ đơn thuần là việc ghi nhớ các cú pháp khô khan, mà đó là chìa khóa vàng giúp bạn tự động hóa quy trình làm sạch dữ liệu, bóc tách thông tin và xây dựng những báo cáo phân tích chuyên nghiệp. Từ những hàm cắt chuỗi kinh điển như LEFT, RIGHT, MID cho đến các hàm thế hệ mới vô cùng mạnh mẽ như TEXTBEFORE, TEXTAFTER, mỗi công cụ đều có một vai trò riêng giúp tối ưu hóa hiệu suất làm việc của bạn.

Để biến những kiến thức này thành kỹ năng thực tế, hãy bắt tay vào thực hành ngay trên các bảng tính hàng ngày của bạn. Đừng quên kết hợp các mẹo nhỏ về làm sạch dữ liệu bằng trim, chuẩn hóa định dạng số bằng hàm value và kiểm tra tính tương thích phiên bản để các trang tính của bạn luôn hoạt động trơn tru trên mọi thiết bị.