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

     

2.2.6. HÀM TÌM KIẾM VÀ chũm 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 bé (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 dạng chứa chuỗi yêu cầu tìm (chuỗi mẹ)___start_num: vị trí bước đầu tìm vào chuỗi within_text (mặc định là 1)Một số lưu ý: - kết quả của nhì hàm này là một con số, chỉ vị trí bắt đầu (tính từstart_num) của find_text vào within_text- sử dụng SEARCH() khi hy vọng tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) đã cho tác dụng là 1.

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

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

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

Đây là dạng việc đã được rất nhiều bạn hỏi trên GPE.Cách làm cho là sử dụng hàm FIND() để 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() để bóc phần tên, cùng hàm RIGHT() để bóc phần họ.Để lấy phần tên (First Name), họ dùng công thức sau (giả sử bọn họ tên nằm ở cell A2):

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

Nghĩa là cần sử dụng hàm FIND() để tìm địa chỉ của ký kết tự trắng thứ nhất kể từ mặt trái, ví dụ nó là địa chỉ thứ 5, lúc đó 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: Bộ 300 Đề Thi Thử Môn Tiếng Anh 2019, Đề Thi Thử Thpt Quốc Gia 2019 Môn Tiếng Anh

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

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

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

*
Công thức sinh hoạt D2: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) và ", " & 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 đa số chúng ta hỏi trên GPE.Cách làm giống hệt như bài Trích xuất họ cùng tên ngơi nghỉ trên, tuy vậy có khác một chút, để trích thêm phần tên đệm. Mang sử Họ với 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á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) sẽ cho kết quả là 6, là vị trí của khoảng tầm trắng thứ nhất (sau chữ Karen).Để tìm vị trí của khoảng chừng trắng vật dụng hai, thì các bạn phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng công dụng của FIND(" ", A2) cộng thêm 1:

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

Rồi dùng kết quả của phương pháp này làm cho 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, cần sử dụng hàm FIND() để tìm địa chỉ của lốt chấm (.) rồi gửi vào trong bí quyết của hàm MID() để tìm ký 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 bỏ phần vừa trình diễn ở trên:

*

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

vào Excel tất cả hàm COLUMN(), cho ra công 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 song 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 trong những vấn đề yên cầu sự khôi lỏi một chút, vày tên cột vào bảng tính chạy tự A đến Z, trường đoản cú AA mang đến AZ... Và tính đến tận thuộc là XFD (!)Có một hàm giúp chúng ta tìm địa chỉ cửa hàng tuyệt đối của một cell, sẽ là hàm CELL("address"), ví dụ như $A$2, hoặc $B$10...

Trích:

Hàm CELL(info_type<,reference>)Với info_type là một trong những 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 bỏ trống thì Excel đang lấy ngay cái cell gồm chứa bí quyết CELL().Trong bài bác này, để tìm địa chỉ 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 thương hiệu của cột đó là những chữ cái nằm trong lòng hai lốt dollar ($) trong cái địa chỉ tuyệt đối này.Bắt đầu có tác dụng nhé: sử dụng hàm MID() trích ra chữ cái từ vị trí thứ hai trong địa chỉ tuyệt đối của cell:

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

mẫu khó là chiếc num_chars này đây, vì tên cột thì hoàn toàn có thể là 1, 2, hoặc 3 ký 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

giải thích chút nhé: dùng hàm FIND(), tìm địa điểm của vết $ trong cái showroom tuyệt đối của cell, và ban đầu tìm từ vị trí thứ 3 trong cái địa chỉ cửa hàng này.Tại sao bắt buộc trừ đi 2 ? cách làm trên sẽ đã cho thấy vị trí (là một bé số) của vệt $ máy 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, cần trừ đi 2 tức là trừ tiết kiệm hơn 2 cái $, bây giờ kết quả sẽ đó là số ký 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 chỉnh sẽ như sau:

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

công thức này vận dụng cho chính cell đựng công thức. nếu như muốn tìm thương hiệu cột tại một cell làm sao đó, chúng ta chỉ vấn đề thêm add (hoặc một cái nào đấy tham chiếu đến địa chỉ này) của cell ao ước tìm vào phía sau dòng "address"Ví dụ, hy vọng 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ó tác dụng tìm kiếm một vài đoạn văn và thay thế sửa chữa nó bằng đ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.

Xem thêm: So Sánh Lai Kinh Tế Và Lai Gây Thành, So Sánh Giữa Lai Kinh Tế Và Lai Gây Thành

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ởi một chuỗi khác, phụ thuộc 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í ban đầu tìm loại sẽ ráng thế, tính từ bên trái sang___num_chars: số cam kết tự của chuỗi cần phải thay thế___new_text: chuỗi văn bản sẽ thay thế sửa chữa cho số ký kết tự sẽ chọn vị start_num với num_charsCái khó của hàm này là xác định được bởistart_num với num_chars. Làm sao biết được ban đầu từ đâu và thay thế bao nhiêu chữ? Tôi nhắc nhở nhé:- bạn dùng hàm FIND() hoặc SEARCH() nhằm xác xác định trí bước đầu (start_num)- dùng hàm LEN() để khẳng định số cam kết tự của chuỗi sẽ được sửa chữa (num_chars)Ví dụ: đế núm số 2007 bởi 2008 trong 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

với A1 = Expense Budget for 2007

Hàm SUBSTITUTE

Công dụng: dùng làm thay cầm cố 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(), 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, cần được xử lý___old_text: chuỗi văn bản cần được cố kỉnh thế___new_text: chuỗi văn bản sẽ thay thế sửa chữa vào___instance_num: số lần sửa chữa old_text bởi new_text, nếu bỏ qua thì tất cảold_text kiếm được sẽ được thay thế bằng new_textVí dụ: đế cầm số 2007 bởi 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