HÀM TÌM KÝ TỰ TRONG CHUỖI EXCEL

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 để kiếm tìm vị trí bắt đầu của một chuỗi nhỏ (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 đề xuất kiếm tìm (chuỗi con)___within_text: chuỗi văn uống bản đựng chuỗi đề nghị kiếm tìm (chuỗi mẹ)___start_num: vị trí ban đầu tra cứu trong chuỗi within_text (khoác định là 1)Một số lưu giữ ý: - Kết quả của nhị hàm này là 1 con số, chỉ địa chỉ ban đầu (tính từstart_num) của find_text trong within_text- Dùng SEARCH() khi ước ao tra cứu một chuỗi ngẫu nhiên. Ví dụ: SEARCH(“e”, “Expenses”) sẽ mang lại công dụng là một trong những.

Bạn đang xem: Hàm tìm ký tự trong chuỗi excel

- Dùng FIND() khi ý muốn tra cứu đúng đắn một chuỗi bao gồm khác nhau chữ hoa, chữ hay. Ví dụ: FIND(“e”, “Expenses”) đã đến công dụng là 4. - Nếu không tìm thấy find_text, hàm vẫn báo lỗi #VALUE- cũng có thể dùng đa số ký kết từ bỏ đại diện nlỗi *, ? vào find_text của hàm SEARCH()- Với hàm SEARCH(), nếu còn muốn kiếm tìm chính cam kết trường đoản cú * hoặc ? thì gõ vết ~ trước ký từ đó ( ~* hoặc là ~?)

Trích xuất bọn họ cùng thương hiệu (ví dụ dùng làm trích mang lại thương hiệu giờ đồng hồ Anh, làm lơ tên đệm)

Đây là dạng bài xích tân oán đã làm được siêu nhiều người hỏi bên trên GPE.Các bước làm là sử dụng hàm FIND() để tìm số đông khoảng trắng chia cách giữa họ và tên, sau đó cần sử dụng hàm LEFT() để tách bóc phần thương hiệu, với hàm RIGHT() để bóc phần bọn họ.Để lấy phần tên (First Name), bọn họ sử dụng phương pháp sau (mang sử chúng ta tên nằm tại cell A2):

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

Nghĩa là sử dụng hàm FIND() để tìm vị trí của ký kết tự white trước tiên kể từ bên trái, ví dụ nó là địa chỉ máy 5, khi đó hàm LEFT() vẫn xác minh được cái tên này có có 4 chữ (= 5-1).

Xem thêm: Lời Bài Hát Suong Lanh Chieu Dong, Sương Lạnh Chiều Đông Lời Bài Hát

Để lấy phần bọn họ (Last Name), họ cần sử dụng công thức:

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

quý khách hàng từ bỏ dịch câu này nhé! Hình sau đây là một vài ví dụ của bài xích vừa rồi.

*
Công thức sinh hoạt D2: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) và ", " và LEFT(A2, FIND(" ", A2) - 1)

Trích xuất chúng ta, tên đệm và tên (ví dụ cùng với thương hiệu giờ Anh, phần thương hiệu đệm được viết tắt)

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

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

Công thức FIND(" ", A2) sẽ mang đến tác dụng là 6, là địa chỉ của khoảng trắng trước tiên (sau chữ Karen).Để kiếm tìm địa điểm của khoảng chừng trắng sản phẩm nhì, thì bạn yêu cầu gán địa chỉ ban đầu tra cứu (start_num) là 7, hay là bằng công dụng của FIND(" ", A2) thêm vào đó 1:

=FIND(" ", A2, FIND(" ",A2) + 1)

Rồi sử dụng kết quả của phương pháp này có tác dụng tđắm đuối số đến 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, sử dụng hàm FIND() nhằm tìm kiếm địa điểm của dấu chnóng (.) rồi gửi vào vào phương pháp của hàm MID() để kiếm tìm ký từ đứng trước vệt chấm:

=MID(A2, FIND(".", A2) - 1, 1) → E

Hình sau đó là một minch họa bỏ phần vừa trình diễn sống trên:

*

Xác định thương hiệu của cột (Determining the Column Letter)

Trong Excel bao gồm hàm COLUMN(), cho ra tác dụng là số của cột (ví dụ, gõ hàm này trong cột B thì công dụng sẽ là 2). Nhưng nhiều lúc bạn có nhu cầu công dụng là tên gọi của cột chđọng không thích chính là số lượng (B chđọng không phải là 2), thì có tác dụng sao?Đây là 1 vụ việc yên cầu sự khôi lỏi một chút ít, bởi thương hiệu cột vào bảng tính chạy tự A cho Z, tự AA cho AZ... với cho đến tận cùng là XFD (!)Có một hàm góp chúng ta kiếm tìm liên quan tuyệt vờ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 tđắm đuối số đã làm được quan niệm (đang nói kỹ hơn trong số những bài sau)Và reference là cell nhưng bạn chỉ định, ví như quăng quật trống thì Excel vẫn rước ngay dòng cell tất cả cất phương pháp CELL().Trong bài bác này, nhằm tìm ảnh hưởng tuyệt đối hoàn hảo của một cell, chúng ta vẫn cần sử dụng công thức CELL() cùng với info_type là "address" Tinh ý một ít, ta thấy tên của cột đó là hầu như vần âm nằm giữa nhị vệt dollar ($) vào loại địa chỉ hoàn hảo này.Bắt đầu có tác dụng nhé: sử dụng hàm MID() trích ra chữ cái trường đoản cú vị trí thứ hai vào cửa hàng hoàn hảo và tuyệt vời nhất của cell:

=MID(CELL("Address"), 2, num_chars)

Cái nặng nề là loại num_chars này trên đây, vị thương hiệu cột thì rất có thể là 1, 2, hoặc 3 ký kết trường đoản cú (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi:

FIND("$", CELL("address"”,A2), 3) - 2

Giải phù hợp chút nhé: Dùng hàm FIND(), tìm kiếm địa chỉ của vệt $ trong dòng xúc tiến hoàn hảo nhất của cell, và bước đầu tìm kiếm tự vị trí sản phẩm công nghệ 3 vào cái tương tác này.Tại sao đề nghị trừ đi 2 ? Công thức trên đã đã cho thấy địa điểm (là 1 trong những bé số) của vệt $ thiết bị nhì trong tương tác hoàn hảo của cell, có nghĩa là dòng vết $ vùng sau thương hiệu cột, yêu cầu trừ đi 2 Có nghĩa là trừ ngắn hơn 2 mẫu $, từ bây giờ công dụng đã chính là số ký kết từ bỏ của thương hiệu cột (1 chữ, 2 chữ hoặc 3 chữ)Bây giờ đồng hồ, bí quyết hoàn hảo sẽ như sau:

=MID(CELL("Address"), 2, FIND("$", CELL("address"), 3) - 2)

Công thức này vận dụng cho bao gồm cell đựng bí quyết. Nếu mong mỏi kiếm tìm tên cột trên một cell nào đó, các bạn chỉ việc thêm shop (hoặc một cái gì đó tmê mẩn chiếu đến liên hệ này) của cell mong kiếm tìm vào vùng sau chiếc "address"lấy ví dụ như, mong search tên của cell AGH68, bạn gõ:

=MID(CELL("Address", AGH68), 2, FIND("$", CELL("address", AGH68), 3) - 2) → AGH

Có những chương trình có chức năng search tìm một trong những đoạn văn uống và sửa chữa nó bằng đoạn văn khác. Excel cũng có khả năng có tác dụng chuyện kia bằng phương pháp sử dụng hàm. Đó là hàmREPLACE cùng 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ông giống, phụ thuộc số ký từ bỏ được chỉ địnhCông thức: =REPLACE(old_text,start_num,num_chars,new_text)___old_text: chuỗi vnạp năng lượng bạn dạng cần được xử lý___start_num: địa điểm bước đầu kiếm tìm cái sẽ thay thế sửa chữa, tính từ phía bên trái sang___num_chars: số cam kết từ bỏ của chuỗi cần phải ráng thế___new_text: chuỗi vnạp năng lượng bản sẽ sửa chữa mang đến số ký kết từ vẫn chọn bởi vì start_num cùng num_charsCái khó của hàm này là khẳng định được bởistart_num cùng num_chars. Làm sao biết được bước đầu trường đoản cú đâu cùng thay thế bao nhiêu chữ? Tôi lưu ý nhé:- Bạn sử dụng hàm FIND() hoặc SEARCH() nhằm xác xác định trí ban đầu (start_num)- Dùng hàm LEN() để xác minh số cam kết từ bỏ của chuỗi sẽ tiến hành thay thế sửa chữa (num_chars)Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007Dùng cách làm nhỏng 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 nhằ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(), tuy thế dễ dàng sử dụng hơn.Công thức: =SUBSTITUTE(text, old_text, new_text <,instance_num>)___text: chuỗi văn uống phiên bản nơi bắt đầu, cần phải xử lý___old_text: chuỗi vnạp năng lượng phiên bản rất cần được ráng thế___new_text: chuỗi văn uống bản vẫn sửa chữa vào___instance_num: tần số sửa chữa thay thế old_text bởi new_text, nếu bỏ lỡ thì tất cảold_text kiếm được sẽ tiến hành thay thế bằng new_textVí dụ: đế nắm số 2007 bởi 2008 trong câu Expense Budget for 2007Dùng cách làm nlỗi sau:

=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008