TÌM KÝ TỰ TRONG CHUỖI

     

2.2.6. HÀM TÌM KIẾM VÀ vậy 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 cùng 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 bạn dạng cần tìm (chuỗi con)___within_text: chuỗi văn phiên bản chứa chuỗi đề nghị tìm (chuỗi mẹ)___start_num: vị trí ban đầu tìm trong chuỗi within_text (mặc định là 1)Một số giữ ý: - công dụng của hai hàm này là 1 con số, chỉ vị trí ban đầu (tính từstart_num) của find_text vào within_text- sử dụng SEARCH() khi muốn tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) đã cho kết quả là 1.

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

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

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

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

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

Nghĩa là 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ừ mặt trái, lấy ví dụ nó là địa chỉ thứ 5, lúc ấy hàm LEFT() sẽ xác định được cái brand name này gồm bao gồm 4 chữ (= 5-1).

Xem thêm: Vũ Nương Dỗ Dành Con Bằng Cách Nào

Để đem phần chúng ta (Last Name), bọn họ 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ụ như của bài xích vừa rồi.

*
Công thức sinh sống D2: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) & ", " & LEFT(A2, FIND(" ", A2) - 1)

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

Đây cũng chính là dạng câu hỏi đã được rất nhiều người hỏi trên GPE.Cách làm y hệt như bài Trích xuất họ cùng tên ở trên, mặc dù có khác một chút, để trích thêm phần tên đệm. đưa sử Họ và Tên (full name) nằm tại vị trí cell A2, và đang xuất hiện 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 thương hiệu (first name):

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

bí quyết FIND(" ", A2) vẫn cho công dụng là 6, là địa điểm của khoảng trắng trước tiên (sau chữ Karen).Để tìm địa chỉ của khoảng trắng trang bị hai, thì chúng ta phải gán vị trí ban đầu tìm (start_num) là 7, hay những bằng công dụng của FIND(" ", A2) cộng thêm 1:

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

Rồi dùng công dụng của phương pháp này làm tham số đến hàm RIGHT() để trích ra phần chúng ta (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() nhằm tìm địa điểm của vệt chấm (.) rồi chuyển vào trong công thức của hàm MID() để tìm cam kết tự đứng trước vệt chấm:

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

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

*

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

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

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

loại khó là cái num_chars này đây, bởi vì 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). Vận dụng hàm FIND thôi:

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

lý giải chút nhé: sử dụng hàm FIND(), tìm địa điểm của vết $ vào cái địa chỉ tuyệt đối của cell, và ban đầu tìm từ vị trí thứ 3 vào cái add này.Tại sao phải trừ đi 2 ? cách làm trên sẽ chỉ ra vị trí (là một con số) của vết $ thứ hai trong add tuyệt đối của cell, có nghĩa là cái lốt $ vùng sau tên cột, yêu cầu trừ đi 2 có nghĩa là trừ tiết kiệm hơn 2 chiếc $, lúc này kết 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ông thức hoàn hảo sẽ như sau:

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

phương pháp này vận dụng cho chủ yếu cell chứa công thức. nếu như muốn tìm thương hiệu cột tại một cell nào đó, các bạn chỉ việc thêm showroom (hoặc một cái nào đấy tham chiếu đến showroom này) của cell ước ao tìm vào phía sau mẫu "address"Ví dụ, mong tìm thương hiệu của cell AGH68, chúng ta gõ:

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

Có các chương trình có công dụng tìm kiếm một vài đoạn văn và thay thế sửa chữa nó bởi đoạn văn khác. Excel cũng có công dụng làm chuyện đó bằng phương pháp dùng hàm. Đó là hàmREPLACE và hàm SUBSTITUTE.

Xem thêm: Có Mấy Kiểu Mối Nối Dây Dẫn Điện ? Có Mấy Loại Mối Nối Dây Dẫn Điện

Hàm REPLACE

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

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