2.2.6. HÀM TÌM KIẾM VÀ THAY THẾ MỘT CHUỖI (Searching for Substrings, Substituting One Substring for Another)

Tác giả: Bùi Nguyễn Triệu Tường (BNTT – GPE)Tổng hợp: phamnhukhang (GPE)

Hàm FIND và Hàm SEARCH

Công dụng: Dùng để tìm vị trí bắt đầu của một chuỗi con (substring) trong một chuỗi Công thức: __ =FIND(find_text, within_text <, start_num>)__ =SEARCH(find_text, within_text <, start_num>)___find_text: chuỗi văn bản cần tìm (chuỗi con)___within_text: chuỗi văn bản chứa chuỗi cần tìm (chuỗi mẹ)___start_num: vị trí bắt đầu tìm trong chuỗi within_text (mặc định là 1)Một số lưu ý: – Kết quả của hai hàm này là một con số, chỉ vị trí bắt đầu (tính từstart_num) của find_text trong within_text- Dùng SEARCH() khi muốn tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) sẽ cho kết quả là 1.

Đang xem: Tìm chuỗi ký tự trong excel

– Dùng FIND() khi muốn tìm chính xác một chuỗi có phân biệt chữ hoa, chữ thường. Ví dụ: FIND(“e”, “Expenses”) sẽ cho kết quả là 4. – Nếu không tìm thấy find_text, hàm sẽ báo lỗi #VALUE- Có thể dùng những ký tự đại diện như *, ? trong find_text của hàm SEARCH()– Với hàm SEARCH(), nếu muốn tìm chính ký tự * hoặc ? thì gõ dấu ~ trước ký tự đó ( ~* hoặc là ~?)

Trích xuất họ và tên (ví dụ dùng để trích cho tên tiếng Anh, bỏ qua tên đệm)

Đây là dạng bài toán đã được rất nhiều bạn hỏi trên GPE.Cách làm là dùng hàm FIND() để tìm những khoảng trắng phân cách giữa họ và tên, sau đó dùng hàm LEFT() để tách phần tên, và hàm RIGHT() để tách phần họ.Để lấy phần tên (First Name), chúng ta dùng công thức sau (giả sử họ tên nằm ở cell A2):

=LEFT(A2, FIND(” “, A2) – 1)

Nghĩa là dùng hàm FIND() để tìm vị trí của ký tự trắng đầu tiên kể từ bên trái, ví dụ nó là vị trí thứ 5, khi đó hàm LEFT() sẽ xác định được cái tên này gồm có 4 chữ (= 5-1).

Xem thêm:

Để lấy phần họ (Last Name), chúng ta dùng công thức:

=RIGHT(A2, LEN(A2) – FIND(” “, A2))

Bạn tự dịch câu này nhé! Hình sau đây là một số ví dụ của bài vừa rồi.

*

Công thức ở D2: =RIGHT(A2, LEN(A2) – FIND(” “, A2)) & “, ” & LEFT(A2, FIND(” “, A2) – 1)

Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt)

Đây cũng là dạng bài toán đã được rất nhiều bạn hỏi trên GPE.Cách làm giống như bài Trích xuất họ và tên ở trên, tuy nhiên có khác một chút, để trích thêm phần tên đệm. Giả sử Họ và Tên (full name) nằm ở cell A2, và đang có giá trị làKaren E. HammondĐầu tiên, như bài trên, dùng công thức sau để tách phần Tên (first name):

=LEFT(A2, FIND(” “, A2) – 1) → Karen

Công thức FIND(” “, A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen).Để tìm vị trí của khoảng trắng thứ hai, thì bạn phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng kết quả của FIND(” “, A2) cộng thêm 1:

=FIND(” “, A2, FIND(” “,A2) + 1)

Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ (last name):

=RIGHT(A2, LEN(A2) – FIND(” “, A2, FIND(” “, A2) +1)) → Hammond

Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm:

=MID(A2, FIND(“.”, A2) – 1, 1) → E

Hình sau đây là một minh họa cho phần vừa trình bày ở trên:

*

Xác định tên của cột (Determining the Column Letter)

Trong Excel có hàm COLUMN(), cho ra kết quả là số của cột (ví dụ, gõ hàm này trong cột B thì kết quả sẽ là 2). Nhưng đôi khi bạn muốn kết quả là tên của cột chứ không muốn đó là con số (B chứ không phải là 2), thì làm sao?Đây là một vấn đề đòi hỏi sự khôn khéo một chút, vì tên cột trong bảng tính chạy từ A đến Z, từ AA đến AZ… và cho tới tận cùng là XFD (!)Có một hàm giúp chúng ta tìm địa chỉ tuyệt đối của một cell, đó là hàm CELL(“address”), ví dụ $A$2, hoặc $B$10…

Trích:

Hàm CELL(info_type<,reference>)Với info_type là một tham số đã được định nghĩa (sẽ nói kỹ hơn trong những bài sau)Và reference là cell mà bạn chỉ định, nếu bỏ trống thì Excel sẽ lấy ngay cái cell có chứa công thức CELL().Trong bài này, để tìm địa chỉ tuyệt đối của một cell, chúng ta sẽ dùng công thức CELL() với info_type là “address”

Tinh ý một chút, ta thấy tên của cột chính là những chữ cái nằm giữa hai dấu dollar ($) trong cái địa chỉ tuyệt đối này.Bắt đầu làm nhé: dùng hàm MID() trích ra chữ cái từ vị trí thứ 2 trong địa chỉ tuyệt đối của cell:

=MID(CELL(“Address”), 2, num_chars)

Cái khó là cái num_chars này đây, vì tên cột thì có thể là 1, 2, hoặc 3 ký tự (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi:

FIND(“$”, CELL(“address””,A2), 3) – 2

Giải thích chút nhé: Dùng hàm FIND(), tìm vị trí của dấu $ trong cái địa chỉ tuyệt đối của cell, và bắt đầu tìm từ vị trí thứ 3 trong cái địa chỉ này.Tại sao phải trừ đi 2 ? Công thức trên sẽ chỉ ra vị trí (là một con số) của dấu $ thứ hai trong địa chỉ tuyệt đối của cell, tức là cái dấu $ phía sau tên cột, phải trừ đi 2 tức là trừ bớt đi 2 cái $, lúc này kết quả sẽ chính là số ký tự của tên cột (1 chữ, 2 chữ hoặc 3 chữ)Bây giờ, công thức hoàn chỉnh sẽ như sau:

=MID(CELL(“Address”), 2, FIND(“$”, CELL(“address”), 3) – 2)

Công thức này áp dụng cho chính cell chứa công thức. Nếu muốn tìm tên cột tại một cell nào đó, bạn chỉ việc thêm địa chỉ (hoặc một cái gì đó tham chiếu đến địa chỉ này) của cell muốn tìm vào phía sau cái “address”Ví dụ, muốn tìm tên của cell AGH68, bạn gõ:

=MID(CELL(“Address”, AGH68), 2, FIND(“$”, CELL(“address”, AGH68), 3) – 2) → AGH

Có nhiều chương trình có khả năng tìm kiếm một số đoạn văn và thay thế nó bằng đoạn văn khác. Excel cũng có khả năng làm chuyện đó bằng cách dùng hàm. Đó là hàmREPLACE và hàm SUBSTITUTE.

Xem thêm: Garena Free Fire Tiếng Việt Là Gì ? 90% Người Chơi Đều Cách Chuyển Ngôn Ngữ Free Fire Sang Tiếng Việt

Hàm REPLACE

Công dụng: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ địnhCông thức: =REPLACE(old_text,start_num,num_chars,new_text)___old_text: chuỗi văn bản cần được xử lý___start_num: vị trí bắt đầu tìm cái sẽ thay thế, tính từ bên trái sang___num_chars: số ký tự của chuỗi cần được thay thế___new_text: chuỗi văn bản sẽ thay thế cho số ký tự đã chọn bởi start_num và num_charsCái khó của hàm này là xác định được bởistart_num và num_chars. Làm sao biết được bắt đầu từ đâu và thay thế bao nhiêu chữ? Tôi gợi ý nhé:- Bạn dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num)– Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars)Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007Dùng công thức như sau:

=REPLACE(A1, FIND(“2007”, A1), LEN(“2007”), “2008”)→ Expense Budget for 2008

Với A1 = Expense Budget for 2007

Hàm SUBSTITUTE

Công dụng: Dùng để thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng tương tự hàm REPLACE(), nhưng dễ sử dụng hơn.Công thức: =SUBSTITUTE(text, old_text, new_text <,instance_num>)___text: chuỗi văn bản gốc, cần được xử lý___old_text: chuỗi văn bản cần được thay thế___new_text: chuỗi văn bản sẽ thay thế vào___instance_num: số lần thay thế old_text bằng new_text, nếu bỏ qua thì tất cảold_text tìm được sẽ được thay thế bằng new_textVí dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007Dùng công thức như sau:

=SUBSTITUTE(“Expense Budget for 2007”, “2007”, “2008”) → Expense Budget for 2008

Leave a Reply

Your email address will not be published. Required fields are marked *