2.2.6. HÀM TÌM KIẾM VÀ cầm cố 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ới Hàm SEARCH
Công dụng: dùng để tìm vị trí ban đầu của một chuỗi nhỏ (substring) vào 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 phiên bản cần tìm kiếm (chuỗi con)___within_text: chuỗi văn bạn dạng chứa chuỗi yêu cầu 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 lại ý: - kết quả của nhị hàm này là 1 trong con số, chỉ vị trí bước đầu (tính từstart_num) của find_text vào within_text- cần sử dụng SEARCH() khi muốn tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) sẽ cho tác dụng là 1.
Bạn đang xem: Tìm kiếm ký tự trong excel
- dùng FIND() khi mong mỏi tìm đúng đắn một chuỗi tất cả phân biệt chữ hoa, chữ thường. Ví dụ: FIND(“e”, “Expenses”) đã cho tác dụng là 4. - Nếu không tìm kiếm thấy find_text, hàm vẫn báo lỗi #VALUE- rất có thể dùng đa số ký tự thay mặt đại diện như *, ? vào find_text của hàm SEARCH()- cùng với hàm SEARCH(), nếu muốn tìm bao gồm ký trường đoản cú * hoặc ? thì gõ vệt ~ trước ký tự đó ( ~* hoặc là ~?)Trích xuất họ và tên (ví dụ dùng để trích đến tên tiếng Anh, bỏ qua tên đệm)
Đây là dạng câu hỏi đã được rất nhiều người hỏi trên GPE.Cách làm cho là cần sử dụng hàm FIND() nhằm tìm những khoảng tầm trắng phân làn giữa họ với tên, tiếp đến dùng hàm LEFT() để bóc tách phần tên, với hàm RIGHT() để bóc phần họ.Để đem phần thương hiệu (First Name), bọn họ dùng công thức sau (giả sử chúng ta tên nằm tại vị trí cell A2):
=LEFT(A2, FIND(" ", A2) - 1)
Nghĩa là cần sử dụng hàm FIND() nhằm tìm địa chỉ của ký kết tự trắng trước tiên kể từ bên trái, lấy ví dụ nó là địa điểm thứ 5, lúc đó hàm LEFT() sẽ khẳng định được cái thương hiệu này gồm bao gồm 4 chữ (= 5-1).
Để lấy phần bọn họ (Last Name), chúng ta dùng công thức:=RIGHT(A2, LEN(A2) - FIND(" ", A2))
các bạn tự dịch câu này nhé! Hình sau đây là một số ví dụ như của bài vừa rồi.
Trích xuất họ, thương hiệu đệm cùng tên (ví dụ với tên tiếng Anh, phần thương hiệu đệ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 bên trên GPE.Cách làm hệt như bài Trích xuất họ và tên sinh sống trên, mặc dù có không giống một chút, để trích thêm phần thương hiệu đệm. Mang sử Họ và Tên (full name) nằm tại vị trí cell A2, và đang sẵn có giá trị làKaren E. HammondĐầu tiên, như bài bác trên, dùng phương pháp sau để bóc phần tên (first name):
=LEFT(A2, FIND(" ", A2) - 1) → Karen
cách làm FIND(" ", A2) đang cho kết quả là 6, là địa điểm của khoảng chừng trắng đầu tiên (sau chữ Karen).Để tìm địa điểm của khoảng tầm trắng sản phẩm công nghệ hai, thì các bạn phải gán vị trí ban đầu tìm (start_num) là 7, hay những bằng hiệu quả của FIND(" ", A2) cộng thêm 1:
=FIND(" ", A2, FIND(" ",A2) + 1)
Rồi dùng tác dụng của công thức này làm cho tham số mang lại 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 thương hiệu đệm, cần sử dụng hàm FIND() nhằm tìm vị trí của dấu chấm (.) rồi gửi vào trong công thức của hàm MID() nhằm tìm cam kết tự đứng trước lốt chấm:
=MID(A2, FIND(".", A2) - 1, 1) → E
Hình sau đấy là một minh họa dồn phần vừa trình diễn ở trên:
Xác định thương hiệu của cột (Determining the Column Letter)
trong Excel tất cả hàm COLUMN(), mang đến ra công dụng 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 có nhu cầu kết quả là tên gọi của cột chứ không muốn đó là số lượng (B chứ chưa hẳn là 2), thì làm sao?Đây là 1 trong những vấn đề đòi hỏi sự láu lỉnh một chút, vì chưng tên cột vào bảng tính chạy trường đoản cú A đến Z, từ bỏ AA đến AZ... Và cho tới tận cùng là XFD (!)Có một hàm giúp bọn họ tìm showroom tuyệt đối của một cell, chính 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à 1 trong tham số đã được có mang (sẽ nói kỹ hơn trong những bài sau)Và reference là cell mà bạn chỉ định, nếu vứt trống thì Excel đang lấy ngay chiếc cell gồm chứa phương pháp CELL().Trong bài xích này, nhằm tìm địa chỉ cửa hàng tuyệt đối của một cell, chúng ta sẽ dùng phương pháp 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 vết dollar ($) trong cái địa chỉ cửa hàng tuyệt đối này.Bắt đầu có tác dụng nhé: cần sử dụng hàm MID() trích ra chữ cái từ vị trí thứ 2 trong địa chỉ cửa hàng tuyệt đối của cell:
=MID(CELL("Address"), 2, num_chars)
cái khó là mẫu num_chars này đây, vì chưng tên cột thì rất có thể là 1, 2, hoặc 3 cam kết tự (ví dụ: A, AA hoặc AAA). áp dụng hàm FIND thôi:
FIND("$", CELL("address"”,A2), 3) - 2
giải thích chút nhé: sử dụng hàm FIND(), tìm địa điểm của dấu $ vào cái add tuyệt đối của cell, và ban đầu tìm từ địa chỉ thứ 3 vào cái địa chỉ cửa hàng này.Tại sao bắt buộc trừ đi 2 ? phương pháp trên sẽ đã cho thấy vị trí (là một nhỏ số) của dấu $ thiết bị hai trong địa chỉ tuyệt đối của cell, tức là cái lốt $ vùng phía đằng sau tên cột, nên trừ đi 2 tức là trừ tiết kiệm hơn 2 cái $, từ bây giờ kết trái sẽ chính là số cam kết tự của thương hiệu cột (1 chữ, 2 chữ hoặc 3 chữ)Bây giờ, công thức hoàn hảo sẽ như sau:
=MID(CELL("Address"), 2, FIND("$", CELL("address"), 3) - 2)
bí quyết này vận dụng cho thiết yếu cell cất công thức. nếu còn muốn tìm thương hiệu cột trên một cell nào đó, các bạn chỉ bài toán thêm địa chỉ (hoặc một cái nào đấy tham chiếu đến showroom này) của cell hy vọng tìm vào phía sau mẫu "address"Ví dụ, ước ao tìm thương hiệu của cell AGH68, các bạn gõ:
=MID(CELL("Address", AGH68), 2, FIND("$", CELL("address", AGH68), 3) - 2) → AGH
Có những chương trình có khả năng tìm kiếm một số trong những đoạn văn và sửa chữa nó bởi đoạn văn khác. Excel cũng có khả năng làm chuyện đó bằng phương pháp dùng hàm. Đó là hàmREPLACE và hàm SUBSTITUTE.
Hàm REPLACE
Công dụng: dùng để thay thế một trong những phần của chuỗi bằng một chuỗi khác, phụ thuộc vào số ký kết tự được chỉ địnhCông thức: =REPLACE(old_text,start_num,num_chars,new_text)___old_text: chuỗi văn bạn dạng cần được xử lý___start_num: vị trí bước đầu tìm cái sẽ vậy thế, tính từ phía bên trái sang___num_chars: số ký kết tự của chuỗi cần phải thay thế___new_text: chuỗi văn phiên bản sẽ thay thế sửa chữa cho số ký tự đang chọn bởi vì start_num với num_charsCái nặng nề của hàm này là xác minh được bởistart_num cùng num_chars. Làm thế nào biết được bắt đầu từ đâu và thay thế sửa chữa bao nhiêu chữ? Tôi lưu ý nhé:- chúng ta dùng hàm FIND() hoặc SEARCH() nhằm xác xác định trí bước đầu (start_num)- cần sử dụng hàm LEN() để xác minh số ký tự của chuỗi đang được thay thế (num_chars)Ví dụ: đế cố số 2007 bởi 2008 vào câu Expense Budget for 2007Dùng phương pháp như sau:
=REPLACE(A1, FIND("2007", A1), LEN("2007"), "2008")→ Expense Budget for 2008
cùng với A1 = Expense Budget for 2007
Hàm SUBSTITUTE
Công dụng: dùng để làm thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng giống như hàm REPLACE(), cơ mà dễ sử dụng hơn.Công thức: =SUBSTITUTE(text, old_text, new_text <,instance_num>)___text: chuỗi văn phiên bản gốc, rất cần phải xử lý___old_text: chuỗi văn bạn dạng cần được thay thế___new_text: chuỗi văn phiên bản sẽ sửa chữa thay thế vào___instance_num: số lần sửa chữa thay thế old_text bằng new_text, nếu bỏ qua mất thì vớ cảold_text kiếm được sẽ được thay thế bằng new_textVí dụ: đế cầm cố số 2007 bởi 2008 vào câu Expense Budget for 2007Dùng công thức như sau:
=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008
