HÀM TÌM KIẾM CHUỖI KÝ TỰ TRONG 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ới Hàm SEARCH

Công dụng: Dùng để tìm kiếm vị trí bước đầu của một chuỗi bé (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 vnạp năng lượng bản buộc phải kiếm tìm (chuỗi con)___within_text: chuỗi văn bản chứa chuỗi phải tra cứu (chuỗi mẹ)___start_num: vị trí ban đầu tìm kiếm vào chuỗi within_text (mang định là 1)Một số giữ ý: - Kết trái của hai hàm này là một trong những con số, chỉ địa điểm ban đầu (tính từstart_num) của find_text vào within_text- Dùng SEARCH() lúc mong kiếm tìm một chuỗi ngẫu nhiên. Ví dụ: SEARCH(“e”, “Expenses”) sẽ đến tác dụng là một trong những.

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

- Dùng FIND() Khi muốn tìm đúng mực một chuỗi có biệt lập chữ hoa, chữ hay. Ví dụ: FIND(“e”, “Expenses”) đang mang lại công 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- cũng có thể sử dụng các ký kết trường đoản cú thay mặt đại diện nhỏng *, ? trong find_text của hàm SEARCH()- Với hàm SEARCH(), nếu như muốn kiếm tìm chủ yếu ký từ bỏ * hoặc ? thì gõ dấu ~ trước ký kết tự kia ( ~* Hoặc là ~?)

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

Đây là dạng bài bác toán đã làm được cực kỳ nhiều người hỏi trên GPE.Cách chế biến là sử dụng hàm FIND() để kiếm tìm các khoảng tầm white chia cách thân chúng ta cùng thương hiệu, sau đó dùng hàm LEFT() nhằm bóc phần thương hiệu, với hàm RIGHT() để bóc phần bọn họ.Để rước phần thương hiệu (First Name), bọn họ dùng phương pháp sau (mang sử bọn họ thương hiệu nằm tại cell A2):

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

Nghĩa là sử dụng hàm FIND() để kiếm tìm vị trí của ký từ White đầu tiên Tính từ lúc phía trái, ví dụ nó là địa chỉ sản phẩm 5, lúc đó hàm LEFT() vẫn xác định được cái brand name này tất cả bao gồm 4 chữ (= 5-1).

Xem thêm: Download Windows 8

Để rước phần chúng ta (Last Name), họ 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 làm việc D2: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) & ", " và LEFT(A2, FIND(" ", A2) - 1)

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

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

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

Công thức FIND(" ", A2) đã mang lại kết quả là 6, là địa chỉ của khoảng tầm trắng trước tiên (sau chữ Karen).Để search địa chỉ của khoảng chừng white lắp thêm hai, thì các bạn buộc phải gán địa chỉ bước đầu tìm (start_num) là 7, Hoặc là bởi tác dụng của FIND(" ", A2) thêm vào đó 1:

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

Rồi cần sử dụng công dụng của bí quyết này có tác dụng tđắm say số mang đế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, cần sử dụng hàm FIND() để tra cứu địa điểm của lốt chnóng (.) rồi đưa vào vào bí quyết của hàm MID() nhằm tìm kiếm cam kết trường đoản cú đứng trước dấu chấm:

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

Hình sau đó là một minh họa cho phần vừa trình bày ngơi nghỉ trên:

*

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

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

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

Cái khó là mẫu num_chars này phía trên, vì tên cột thì có thể là 1 trong, 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(), search vị trí của vết $ vào mẫu hệ trọng tuyệt vời của cell, cùng bước đầu search từ bỏ vị trí sản phẩm 3 vào mẫu địa chỉ này.Tại sao buộc phải trừ đi 2 ? Công thức bên trên vẫn đã cho thấy địa chỉ (là 1 trong những con số) của lốt $ trang bị nhì trong tác động hoàn hảo và tuyệt vời nhất của cell, tức là dòng dấu $ phía đằng sau thương hiệu cột, buộc phải trừ đi 2 tức là trừ ngắn hơn 2 chiếc $, hôm nay hiệu quả sẽ chính là số ký kết từ của thương hiệu cột (1 chữ, 2 chữ hoặc 3 chữ)Bây giờ, cách làm hoàn chỉnh sẽ như sau:

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

Công thức này vận dụng mang đến bao gồm cell đựng bí quyết. Nếu mong mỏi tìm tên cột trên một cell làm sao đó, các bạn chỉ câu hỏi thêm thúc đẩy (hoặc một cái nào đấy tham chiếu cho liên can này) của cell mong muốn tìm vào phía đằng sau dòng "address"Ví dụ, hy vọng tra cứu tên của cell AGH68, chúng ta gõ:

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

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

Hàm REPLACE

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

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