Cách xử lý dữ liệu dạng Text với các hàm trong Excel (Phần 4)

Bạn muốn tìm kiếm tên bạn trong danh sách dài? Muốn thay thế tất cả chữ ''t'' thành ''T'' trong file Excel? Bạn có biết hàm FIND trong Excel, hàm REPLACE? Cùng tìm hiểu nhé!
 

Cách tìm kiếm và thay thế dữ liệu định dạng Text trong Excel

Tìm kiếm 1 đoạn ký tự: Hàm FIND trong Excel và hàm SEARCH

1. Với hàm FIND trong Excel

=FIND(find_text, within_text, start_num)

2. Với hàm SEARCH

=SEARCH(find_text, within_text, start_num)

Trong đó: ở cả 2 hàm

• find_text: Tìm cái gì
• within_text: Tìm ở đâu
• start_num: Tìm ở vị trí thứ mấy

Điểm khác biệt duy nhất giữa 2 hàm này là: Hàm FIND trong Excel có phân biệt chữ cái viết hoa và viết thường. Còn hàm SEARCH thì không phân biệt ký tự viết hoa. 

Ví dụ: Giả sử bạn cần tìm chữ đoạn trong cụm "Tìm đoạn văn sau" trong ô A1. Nếu bạn dùng hàm FIND(''Đoạn'', A1) thì hàm sẽ bị lỗi #VALUE! do không tìm được, nhưng nếu bạn dùng hàm SEARCH("Đoạn", A1) thì hàm vẫn trả được về kết quả là 5.

Thay thế 1 đoạn ký tự bằng 1 đoạn khác: Hàm REPLACE và hàm SUBSTITUTE

Với hàm REPLACE

=REPLACE(old_text,start_num,num_chars,new_text)

Trong đó

• old_text: Đoạn text ban đầu
• start_num: Vị trí bắt đầu
• num_chars: Số lượng ký tự cần thay thế
• new_text: Nội dung đoạn text cần thay thế

Hàm này khá phức tạp, bởi nó quy định chặt chẽ về số lượng ký tự, vị trí ký tự và nội dung ký tự.

Với hàm SUBSTITUTE

Thay vì việc viết toàn bộ nội dung hàm, điều khiến Excel khó nhận hàm, hãy xem lại cách viết hàm ở bài viết trong mục "Xem thêm" bên dưới.

=SUBSTITUTE(text,old_text,new_text,instance_num)

Trong đó

• text: Đoạn text nào cần thay thế trong đoạn text ban đầu
• old_text: Đoạn text ban đầu
• new_text: Nội dung đoạn text cần thay thế
• instance_num: Vị trí ký tự thứ mấy

Hàm REPLACE, bạn cần các con số (start_num, num_chars) để phản ánh đoạn text cần thay thế là đoạn text nào dựa trên vị trí bắt đầu của đoạn text đó và vị trí ký tự của đoạn. Nhưng với hàm SUBSTITUTE này, bạn được phép đưa luôn đoạn text ban đầu vào, dễ hơn nhiều so với hàm REPLACE.

Tuy nhiên, khi tham số old_text là nội dung trực tiếp ("abc" thay vì B12) thì bạn sẽ có khả năng nội dung này sẽ bị trùng lặp, xuất hiện nhiều lần trong cùng 1 ô, đó là lý do tại sao bạn cần sử dụng instance_num để phân biệt.

Ví dụ về cách thay thế bằng hàm REPLACE

Yêu cầu: Trong vị trí ô B7 chứa nội dung: ''Thay thế 1 đoạn ký tự bằng 1 đoạn khác". Ở đây, chữ "1 đoạn'' xuất hiện 2 lần, và bạn cần thay thế chữ ''1 đoạn'' ở vị trí đầu tiên bằng 1 ký tự khác là abc.

Cách 1: Dùng hàm REPLACE đối với 1 đối tượng đầu tiên

=REPLACE(B7, SEARCH(''1 đoạn", B7), LEN("1 đoạn"), ''abc'')

Trong đó

• B7: Ô chứa đoạn text ban đầu
• SEARCH("1 đoạn", B7): Để tìm vị trí của "1 đoạn" nằm ở đâu trong cả đoạn B7
• LEN("1 đoạn"): Để đếm xem cụm từ này có bao nhiêu ký tự
• ''abc'': Là ký tự cần thay thế vào

Lưu ý: Nên kiểm tra lại dấu nháy kép ("") khi nhập công thức vào ô. Nếu bạn copy y hệt công thức trên mạng hay của bài viết này về, có thể Excel sẽ không nhận hàm hoặc hàm bị lỗi.

Kết quả như sau:

Cách 2: Dùng hàm SUBSTITUTE đối với 1 đối tượng nhưng xuất hiện ở nhiều vị trí

=SUBSTITUTE(B7, "1 đoạn", "abc")

Trong đó: [instance_num] không được điền, do đây là tham số không bắt buộc, nếu không điền thì Excel sẽ tự hiểu là bắt đầu từ vị trí đầu tiên luôn. Bạn có thể nhập hay không nhập đều được.

Bạn cùng xem kết quả trả về:

Kết luận

1. Hàm REPLACE sẽ áp dụng trong trường hợp bạn xác định đoạn ký tự cụ thể mà bạn muốn thay thế. Với hàm này, bạn không sợ nếu đoạn text được lặp đi lặp lại ở nhiều vị trí khác nhau của 1 nội dung
2. Hàm SUBSTITUTE bạn sẽ dùng khi bạn muốn thay thế toàn bộ 1 đoạn ký tự nào đó thành 1 ký tự khác.

Hãy cùng nhau áp dụng vào các bài tập ví dụ bên dưới để hiểu rõ cũng như thực hành kỹ năng này nhé!

Ví dụ tìm kiếm và thay thế dữ liệu định dạng Text trong Excel

Dưới đây là bài tập được thiết kế để giúp bạn đọc hình dung được cách sử dụng các hàm đã được giới thiệu ở trên. Cùng luyện tập tại file đính kèm bài viết này nhé! Gitiho đã để link ở trên đầu bài viết (Sheet Text_TimKiem_ThayThe).

Yêu cầu 1: Thay thế ký tự định dạng Text trong Excel

Trong trường hợp này, bạn cần sử dụng hàm SUBSTITUTE như sau:

=SUBSTITUTE(B11, "Hà Nội", "TP HCM")

Lưu ý:

• Cần viết đúng chính tả và chữ cái in hoa, vì rất có thể nếu không đúng chính tả và chữ cái in hoa, nội dung text sẽ không tìm thấy được.
• Nên kiểm tra lại dấu nháy kép ("") khi nhập công thức vào ô. Nếu bạn copy y hệt công thức trên mạng hay của bài viết này về, có thể Excel sẽ không nhận hàm hoặc hàm bị lỗi.
• [instance_num] không cần điền cũng được, do không có yếu tố lặp lại đoạn text này trong nội dung.

Cùng xem kết quả có giống như hình dưới đây không nhé:

Rất đơn giản phải không nào?

Yêu cầu 2: Tìm ký tự định dạng Text trong Excel kèm điều kiện

Nếu tìm vị trí dấu cách thứ nhất thì bạn có thể điền mục start_num là 1 hoặc bỏ qua, không điền. Bạn dùng công thức:

=SEARCH(" ", B12, 2)

Ở đây, bạn cần tìm vị trí dấu cách thứ 2 chứ không phải thứ nhất. Nên bạn cần dùng hàm như sau:

=SEARCH(" ", B12, SEARCH(" ", B12)+1)

Trong đó: start_num là SEARCH(" ", B12)+1, tức là tìm ở vị trí sau dấu cách đầu tiên 1 đơn vị. Thì bạn sẽ có được vị trí của dấu cách thứ 2 trong ô B12.

Kết quả như sau:

Yêu cầu 3: Xử lý nhiều yêu cầu với ký tự định dạng Text trong Excel trên 1 công thức hàm

Phân tích yêu cầu đề bài:

1. Thay thế dấu _ thành dấu cách: Dùng hàm SUBSTITUTE

=SUBSTITUDE(B13, "_", " ")

2. Viết hoa chữ cái đầu mỗi từ: Dùng hàm PROPER (Đọc chi tiết tại bài viết mục "Xem thêm" bên dưới)

Công thức cần nhập là:

=PROPER(SUBSTITUTE(B13, "_", " "))

Kết quả như sau:

Nguyên tắc khi viết hàm: Phải viết những hàm quan trọng trước, như ở ví dụ trên là thay thế ký tự trước, sau đó mới điều chỉnh chính tả. Nên viết từng phần, sau đó mới ghép chúng lại để được kết quả theo yêu cầu.

Kết luận

Hy vọng bài viết này đã mang đến cho bạn các cách xử lý dữ liệu định dạng Text trong Excel, đặc biệt là cách tìm kiếm và thay thế ký tự Text bằng các hàm FIND trong Excel hay hàm REPLACE. Đừng quên luyện tập với file đính kèm bài viết và đọc hết các phần của bài viết này để thành thạo kỹ năng về các hàm xử lý dữ liệu dạng Text nhé!