Kỹ thuật Substring trong Excel: Trích xuất và Định dạng Văn bản

Trích xuất văn bản theo vị trí bằng các hàm substring trong Excel

Chúng ta thường muốn trích xuất các phần cụ thể của một chuỗi văn bản, như một từ hoặc một dãy ký tự, dựa trên vị trí của chúng. Excel cung cấp một số hàm tích hợp để giúp bạn trích xuất các phần nhất định của chuỗi văn bản. Một số hàm phổ biến nhất là LEFT(), RIGHT(), MID(), TEXTBEFORE() và TEXTAFTER(). Hãy cùng xem từng hàm. 

Sử dụng hàm Excel substring LEFT()

Bạn có thể dùng hàm LEFT() để trích xuất các ký tự từ đầu một chuỗi văn bản. Cú pháp như sau:

=LEFT(text, [num_chars])

Trong đó: 

• text là địa chỉ ô chứa chuỗi văn bản gốc.

• num_chars là số ký tự cần trích xuất. Giá trị mặc định là 1.

Xem một ví dụ: Ở đây tôi có một số mã sản phẩm và muốn trích xuất ba ký tự đầu tiên của chúng. 

=LEFT(A2,3)

Trích ba ký tự đầu bằng hàm LEFT(). Hình: Tác giả.

Sử dụng hàm Excel substring RIGHT()

Hàm RIGHT() trong Excel trích xuất các ký tự từ cuối một chuỗi văn bản. Cú pháp như sau:

=Right(text, [num_chars])

Trong đó: 

• text là địa chỉ ô chứa chuỗi văn bản gốc.

• num_chars là số ký tự cần trích xuất. Giá trị mặc định là 1.

Ví dụ: Ở đây tôi dùng hàm RIGHT() để trích ba ký tự cuối.

=RIGHT(A2, 3)

Trích ba ký tự cuối bằng hàm RIGHT(). Hình: Tác giả.

Sử dụng hàm Excel substring MID()

Hàm MID() trích xuất các ký tự ở giữa một chuỗi văn bản. Cú pháp như sau:

=MID(text, start_num, num_chars)

Trong đó:

• text là địa chỉ ô chứa chuỗi văn bản gốc.

• num_chars là số ký tự cần trích xuất. Giá trị mặc định là 1.

• start_num là vị trí bắt đầu trong chuỗi văn bản.

Trong ví dụ này, tôi dùng công thức MID() để trích giá trị ở giữa chuỗi, bắt đầu từ ký tự thứ tư.

=MID(A2,4,3)

Trích ba ký tự ở giữa bằng hàm MID(). Hình: Tác giả. 

Xong rồi. =MID(A2,4,3) trích ba ký tự từ văn bản trong ô A2, bắt đầu từ ký tự thứ 4.

Sử dụng hàm Excel substring FIND()

Hàm FIND() trong Excel xác định vị trí của một chuỗi con trong một chuỗi văn bản và trả về vị trí ký tự đầu tiên của chuỗi con đó. Cú pháp như sau:

FIND(find_text, within_text, [start_num]) 

Trong đó:

• find_text là văn bản cần tìm.

• within_text là văn bản chứa phần cần tìm.

• start_num là vị trí bắt đầu tìm kiếm, mặc định là 1.

Hãy xét một bộ dữ liệu đơn giản để minh họa cách hàm FIND() hoạt động. Trong ví dụ này, tôi có tên DataCamp ở cột Text và muốn xác định vị trí của chuỗi con Camp. Kết quả trả về 5 vì Camp bắt đầu ở ký tự thứ năm trong văn bản.

=FIND("Camp", A2)

Sử dụng hàm FIND() để lấy vị trí của văn bản. Hình: Tác giả.

Sử dụng hàm Excel substring SUBSTITUTE()

Hàm SUBSTITUTE() thay thế một chuỗi con cụ thể bằng chuỗi khác. Cú pháp như sau:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Trong đó:

• text chứa văn bản bạn muốn thay đổi.

• old_text là văn bản bạn muốn thay thế.

• new_text là văn bản để thay thế cho văn bản cũ.

• instance_num chỉ định lần xuất hiện nào của văn bản bạn muốn thay. Nếu không chỉ định, mọi lần xuất hiện sẽ được thay thế. 

Để hiểu rõ, hãy xem một bộ dữ liệu đơn giản chứa các chuỗi văn bản với bộ phân tách cụ thể. Cột Text liệt kê tên và độ tuổi được định dạng với dấu chấm phẩy làm bộ phân tách. Để làm sạch, tôi nhập công thức sau:

=SUBSTITUTE(A2, ";" , ",")

Cụ thể:

• A2 là ô chứa văn bản gốc.

• ";" là văn bản cũ cần thay.

• "," là văn bản thay thế.

Sử dụng hàm SUBSTITUTE để thay văn bản cũ. Hình: Tác giả.

Vậy là xong. Bằng cách áp dụng công thức trong ô B2, Excel thay dấu chấm phẩy bằng dấu phẩy.

Trích xuất văn bản theo bộ phân tách bằng các hàm substring trong Excel

Bộ phân tách là các ký tự cụ thể ngăn cách hai chuỗi văn bản, như dấu chấm phẩy hoặc dấu phẩy. Trong Excel, bạn có thể dùng các hàm TEXTBEFORE() và TEXTAFTER() để trích xuất văn bản khi có bộ phân tách.  

Sử dụng hàm Excel substring TEXTBEFORE() 

Đúng như tên gọi, hàm TEXTBEFORE() trích văn bản nằm trước một ký tự hoặc chuỗi ký tự được chỉ định. Cú pháp như sau:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Trong đó: 

• text là văn bản gốc.

• delimiter thường là dấu phẩy hoặc dấu chấm phẩy.

• instance_num là lần xuất hiện mong muốn của bộ phân tách. Giá trị mặc định là 1.

• match_mode cho biết việc tìm kiếm bộ phân tách có phân biệt hoa thường (TRUE, mặc định) hay không (FALSE).

• match_end xác định liệu cuối chuỗi văn bản có được coi là bộ phân tách hay không. Nếu TRUE, hàm sẽ trả về văn bản gốc nếu không tìm thấy bộ phân tách.

• if_not_found chỉ định giá trị tùy chỉnh trả về nếu không tìm thấy bộ phân tách.

Giờ hãy xem ví dụ để thấy công thức hoạt động. Tôi có cột Text chứa thông tin nhân viên, gồm tên, phòng ban và chi nhánh. Tôi muốn trích riêng phần tên và phòng ban.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Sử dụng hàm TEXTBEFORE() để trích dữ liệu. Hình: Tác giả 

Cách hoạt động: 

• A2 chứa văn bản cần trích. 

• "," là bộ phân tách chia văn bản trong dữ liệu gốc. 

• 2 nghĩa là hàm sẽ xét lần xuất hiện thứ hai của bộ phân tách.

• 1 bỏ qua phân biệt hoa thường, nếu có.

• 1 (ở cuối) trả về văn bản gốc nếu không có bộ phân tách.

Sử dụng hàm Excel substring TEXTAFTER()

Hàm TEXTAFTER() tương tự như TEXTBEFORE()—khác biệt nằm ở kết quả. TEXTAFTER() trích văn bản nằm sau bộ phân tách. Cú pháp như sau:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Trong đó: 

• text là văn bản gốc.

• delimiter là dấu phẩy hoặc dấu chấm phẩy. 

• instance_num là lần xuất hiện mong muốn của bộ phân tách. Giá trị mặc định là 1.

• match_mode cho biết việc tìm kiếm bộ phân tách có phân biệt hoa thường (TRUE, mặc định) hay không (FALSE).

• match_end xác định liệu cuối chuỗi văn bản có được coi là bộ phân tách hay không. Nếu TRUE, hàm sẽ trả về văn bản gốc nếu không tìm thấy bộ phân tách.

• if_not_found chỉ định giá trị tùy chỉnh trả về nếu không tìm thấy bộ phân tách.

Ở đây, tôi có cột Text chứa thông tin nhân viên gồm tên, phòng ban và chi nhánh. Từ đó, tôi muốn trích nơi làm việc và ID của nhân viên. 

=TEXTAFTER(A2,",",2,1,1)

Sử dụng hàm TEXTAFTER() để trích dữ liệu. Hình: Tác giả 

Cách hoạt động: 

• A2 chứa văn bản cần trích. 

• ","  là bộ phân tách chia văn bản trong dữ liệu gốc. 

• 2 nghĩa là hàm sẽ xét lần xuất hiện thứ hai của bộ phân tách. 

• 1 bỏ qua phân biệt hoa thường, nếu có.

• 1 (ở cuối) trả về văn bản gốc nếu không có bộ phân tách.

Các trường hợp sử dụng substring trong Excel

Giờ bạn đã nắm các kiến thức cơ bản về substring, hãy tìm hiểu các phương pháp nâng cao. Những phương pháp này cho phép thao tác văn bản phức tạp hơn, giúp quy trình xử lý dữ liệu hiệu quả hơn nữa. 

Xử lý độ dài tên riêng khác nhau

Tôi có một bộ dữ liệu chứa họ và tên đầy đủ, nhưng tôi chỉ muốn trích tên riêng.

Bảng chứa tên đầy đủ. Hình: Tác giả.

Bạn có thể nghĩ đơn giản—dùng LEFT() để lấy tên riêng. Tôi đã thử với công thức:

=LEFT(A2,4)

Dùng hàm LEFT() để lấy tên riêng. Hình: Tác giả.

Nhưng thay vì nhận được tên riêng, tôi chỉ lấy bốn ký tự đầu tiên vì tôi đặt num_char là 4, do thấy Jane có bốn ký tự ở tên riêng. Khi sao chép công thức, các tên khác không cho kết quả như mong muốn.

Tôi cần một giải pháp linh hoạt hơn. Để trích tên riêng bất kể độ dài, bạn có thể kết hợp LEFT() với FIND(). Tôi dùng FIND() để tìm vị trí khoảng trắng đầu tiên trong tên đầy đủ (ngăn cách tên riêng và họ), sau đó dùng LEFT() để trích các ký tự đến vị trí đó. 

=LEFT(A2,FIND(" ",A2)-1)

Kết hợp hàm FIND() và LEFT() để trích tên riêng. Hình: Tác giả.

Phân tích công thức để bạn dễ hiểu hơn:

• Ô A2 chứa tên đầy đủ.

• FIND(" ", A2, 1) tìm vị trí khoảng trắng đầu tiên trong chuỗi văn bản ở ô A2, bắt đầu từ ký tự đầu tiên.

• -1 dùng để loại bỏ khoảng trắng.

• LEFT(A2, ...) trích số ký tự xác định từ bên trái chuỗi văn bản.

Trích miền từ địa chỉ email

Hãy xét ví dụ khác để xem LEFT() và FIND() có xử lý được kịch bản khác không. Tôi có một bộ dữ liệu địa chỉ email từ đó cần trích tên miền.

Dữ liệu chứa địa chỉ email. Hình: Tác giả.

Để làm vậy, tôi tạo cột Email Domain, và chọn một ô khác để nhập công thức sau, rồi sao chép xuống ba ô khác:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Trích tên miền bằng RIGHT(), LEN() và FIND(). Hình: Tác giả.

Vậy là có tất cả tên miền. Nhưng hãy hiểu cách Excel lấy kết quả mong muốn:

• FIND("@", A2) xác định vị trí ký tự @ trong địa chỉ email.

• LEN(A2) tính tổng độ dài địa chỉ email.

• LEN(A2) - FIND("@", A2) tính số ký tự sau ký tự @.

• Cuối cùng, RIGHT(A2, LEN(A2) - FIND("@", A2)) trích bấy nhiêu ký tự từ phía bên phải chuỗi.

Trích mã sản phẩm bằng cách kết hợp LEFT(), MID() và RIGHT()

Đến giờ tôi mới giải thích cách các hàm LEFT(), MID() và RIGHT() hoạt động riêng lẻ. Nhưng bạn có thể làm được nhiều hơn khi kết hợp chúng. Chúng có thể trích các phần khác nhau của chuỗi văn bản dựa trên mẫu cụ thể.

Ở đây, tôi có danh sách mã sản phẩm và tôi muốn trích một phần cụ thể từ chúng. 

• Từ phần đầu tiên, tôi muốn một ký tự.
• Từ phần thứ hai, tôi muốn đủ bốn ký tự.
• Từ phần cuối, tôi muốn trích ba ký tự cuối.

Danh sách mã sản phẩm. Hình: Tác giả.

Để thực hiện, tôi kết hợp các hàm LEFT(), RIGHT() và MID() và nhập công thức sau:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Kết hợp MID(), LEFT() và RIGHT() để trích dữ liệu. Hình: Tác giả.

Như bạn thấy, công thức kết hợp này đã trích đúng các ký tự mong muốn ở từng phần của mã. Cụ thể:

• LEFT(A2, 1) trích ký tự đầu tiên.

• MID(A2, FIND("-", A2) + 1, 4) tìm dấu gạch ngang đầu tiên, dịch sang phải một ký tự, rồi trích bốn ký tự.

• "-" thêm một dấu gạch ngang.

• RIGHT(A2, 3) trích ba ký tự cuối.

• & nối các phần lại với nhau. 

Kết hợp SUBSTITUTE() với MID() để trích xuất động 

Bây giờ, hãy xem cách bạn có thể kết hợp hàm MID() với SUBSTITUTE() để trích các phần cụ thể của chuỗi văn bản và sau đó thay thế ký tự hoặc chuỗi ký tự trong phần đã trích.

Ví dụ, tôi có Order Details của khách hàng và muốn thay mã đơn hàng bằng dòng Order confirmed.

Bảng chứa thông tin đơn hàng. Hình: Tác giả.

Tôi dùng công thức sau:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

Dùng hàm SUBSTITUTE() và MID() để thay văn bản. Hình: Tác giả.

Vậy là xong! Bạn thấy công thức trích đúng phần văn bản như mong muốn.

• MID(A2, 1, 19) trích 19 ký tự đầu từ văn bản trong ô A2.

• SUBSTITUTE(text, old_text, new_text) thay các lần xuất hiện của old_text bằng new_text.

Xem thêm ví dụ khác: Tôi có cột Product Detail và muốn trích kích cỡ sản phẩm trong ngoặc vuông.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Trích kích cỡ sản phẩm trong ngoặc vuông. Hình: Tác giả.

Xong—đã trích toàn bộ thông tin.

• FIND("[", A2) tìm vị trí bắt đầu của ngoặc vuông mở.

• FIND("]", A2) tìm vị trí kết thúc của ngoặc vuông.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) trích văn bản giữa hai dấu ngoặc.

• SUBSTITUTE(...,"[", "") loại bỏ dấu ngoặc vuông mở khỏi phần văn bản đã trích.

Làm sạch các mục dữ liệu

Đôi khi dữ liệu của chúng ta lộn xộn, nhưng không có nghĩa bạn phải dùng chúng như vậy trong công việc. Ở đây, tôi có danh sách số điện thoại ở nhiều định dạng khác nhau, và tôi muốn chuẩn hóa chúng về một định dạng nhất quán bằng cách làm sạch các điểm không đồng nhất.

Số điện thoại chưa định dạng. Hình: Tác giả.

Để chuẩn hóa, tôi tạo cột mới tên Clean data. Tôi nhập công thức sau ở ô thứ hai và sao chép công thức đến mọi ô cần kết quả:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Cụ thể: 

• SUBSTITUTE(A2, "-", "") xóa dấu gạch ngang.

• SUBSTITUTE(..., "(", "") xóa dấu ngoặc mở.

• SUBSTITUTE(..., ")", "") xóa dấu ngoặc đóng.

• SUBSTITUTE(..., ".", "") xóa dấu chấm.

Điều này chuyển nhiều định dạng khác nhau thành một chuỗi số liên tục như bên dưới. Giờ dữ liệu đã sạch, gọn và sẵn sàng sử dụng. Bạn có thể để nguyên như vậy nếu muốn.

Làm sạch dữ liệu bằng hàm SUBSTITUTE(). Hình: Tác giả.

Định dạng văn bản cho báo cáo

Nếu muốn đi xa hơn sau khi làm sạch dữ liệu và định dạng cho phù hợp, bạn cũng có thể dùng các hàm substring khác nhau.

Xét ví dụ trước, nơi tôi đã làm sạch số điện thoại. Bây giờ, tôi muốn thay đổi định dạng các số để trông giống số điện thoại hơn chứ không chỉ là chuỗi chữ số. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Chuẩn hóa định dạng số bằng nhiều hàm substring. Hình: Tác giả.

Xong. Tôi đã có tất cả các số theo ý. Cách công thức hoạt động:

• LEFT(B2, 3) trích ba chữ số đầu.

• MID(B2, 4, 3) trích ba chữ số tiếp theo bắt đầu từ vị trí thứ tư .

• RIGHT(B2, 4) trích bốn chữ số cuối.

• TEXT(..., "000") and TEXT(..., "0000") định dạng từng phần để đảm bảo số lượng chữ số đúng với số 0 ở đầu nếu cần.

• & "-" & nối các phần đã định dạng bằng dấu gạch ngang.

Tổng kết

Bạn đã học cách dùng các hàm substring của Excel như LEFT(), RIGHT(), MID(), TEXTBEFORE() và TEXTAFTER() để xử lý dữ liệu văn bản. Dù là trích các phần cụ thể của chuỗi hay làm sạch dữ liệu lộn xộn, các hàm này đều giúp công việc của bạn dễ dàng và hiệu quả hơn.

Nhưng Excel luôn còn nhiều điều để học. Khóa học Introduction to Excel là bước tiếp theo hoàn hảo nếu bạn mới bắt đầu. Nếu muốn học thêm về phân tích dữ liệu, hãy thử khóa Data Analysis in Excel, rất phù hợp khi kết hợp với Data Manipulation in Excel Cheat Sheet. Những tài liệu này sẽ giúp bạn xây nền tảng vững chắc và nâng tầm kỹ năng.