Hàm TÌM Excel hiển thị vị trí số của ký tự trong một văn bản nhất định. Vị trí bắt đầu của Chuỗi có thể thay đổi dựa trên đối số thứ ba [Start_num]. Kiểm tra
Ví dụ: để tìm lần xuất hiện thứ 2 của 8, =FIND[8,128128,4] trả về 6, bắt đầu tìm 8 từ chữ số thứ 4 trả về 6, i. e. , vị trí thứ 6 trong hình
cú pháp
= TÌM[tìm_văn_bản, trong_văn_bản, [số_bắt_đầu]]
Thông số
find_text – Ký tự cần tìm
within_text – Chuỗi chứa ký tự
start_num [Tùy chọn] – Chỉ định ký tự để bắt đầu tìm kiếm
Giá trị trả về
Kết quả boolean [Đúng hoặc Sai]
Ghi chú chính
- Trong hàm FIND, ký tự đầu tiên trong đối số within_text là ký tự số 1 [vị trí mặc định]
- Hàm TÌM phân biệt chữ hoa chữ thường nhưng cho phép các ký tự đại diện
- Sử dụng hàm TÌM KIẾM excel nếu người dùng muốn thực hiện tìm kiếm không phân biệt chữ hoa chữ thường
Hàm TÌM trả về #VALUE. giá trị lỗi trong trường hợp
- start_num không lớn hơn 0
- start_num lớn hơn độ dài của within_text
- find_text không xuất hiện trong within_text
- Trường hợp nhạy cảm
Hãy kiểm tra các ví dụ
- Nếu bạn muốn định vị một ký tự hoặc bảng chữ cái trong một chuỗi mà không cần lo lắng về chỉ mục, bạn không cần điền đối số 'start_num' như trong ví dụ 1
- Bạn cũng có thể tìm chỉ mục giá trị hoặc ký tự đặc biệt hoặc ký tự đại diện trong từ
- Trong trường hợp bạn muốn tìm sự xuất hiện hoặc trình tự xảy ra sau lần xuất hiện đầu tiên, thì bạn có thể hợp nhất hai công thức,
Giống như nếu bạn sắp xem sự kiện thứ 2, cuối cùng hoặc thứ n, thì bạn có thể sử dụng =FIND[B10,C10, [FIND[B9,C9,D9]+1 hoặc vị trí thứ n]]. Công thức trả về cùng một vị trí ký tự nhưng với vị trí thứ 2 hoặc khác. [Tải về sách bài tập để rõ]
Tuy nhiên, có thể có trường hợp bạn cần tìm vị trí của một thể hiện nhất định của ký tự trong một chuỗi
Ví dụ: bạn có thể cần tìm vị trí của dấu cách cuối cùng trong một chuỗi. Có thể bạn cần nó để tách họ của một người khỏi phần còn lại của tên
Trong các trường hợp khác, bạn có thể có chuỗi được cấu trúc theo định dạng nhất định và bạn có thể cần tìm vị trí của khoảng trắng cuối cùng để có thể xóa nó
Dù lý do là gì, trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn hai cách để tìm khoảng trắng cuối cùng trong một chuỗi trong Excel
- Sử dụng công thức Excel
- Sử dụng tập lệnh VBA
Mục lục
Công thức Excel để tìm khoảng trắng cuối cùng trong một chuỗi trong Excel
Giả sử chúng ta có danh sách các chuỗi sau
Chúng tôi muốn tìm vị trí của ký tự khoảng trắng cuối cùng trong mỗi chuỗi
Đây là công thức mà bạn có thể sử dụng để tìm khoảng trắng cuối cùng trong một chuỗi [có trong tham chiếu ô A2]
=FIND["/", SUBSTITUTE[A2," ","/", LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]]]
Khi bạn nhấn phím quay lại, công thức trên sẽ trả về một số nguyên cho biết vị trí của khoảng trắng cuối cùng trong chuỗi tại tham chiếu ô A2
Sao chép công thức này xuống các ô còn lại
Đây là kết quả bạn sẽ nhận được
Giải thích về công thức
Hãy dành chút thời gian để hiểu cách thức hoạt động của công thức này. Chúng ta sẽ đi qua từng lớp công thức trên, bắt đầu từ lớp trong cùng
- SUBSTITUTE[A2,” “,””] – Công thức này loại bỏ tất cả khoảng trắng khỏi chuỗi trong ô A2. Nó trả về chuỗi “TheReaperIntheNorth”
- LEN[SUBSTITUTE[A2,” “,””]] – Công thức này tìm tổng độ dài của chuỗi sau khi loại bỏ tất cả các khoảng trắng. Nó trả về giá trị 19
- LEN[A2]-LEN[SUBSTITUTE[A2,” “,””]] – Công thức này trừ tổng độ dài của chuỗi không có dấu cách khỏi tổng độ dài của chuỗi ban đầu. Nói cách khác, hàm này tìm ra số khoảng trắng trong chuỗi. Công thức trả về giá trị 4
- SUBSTITUTE[A2,” “,”/”, LEN[A2]-LEN[SUBSTITUTE[A2,” “,””]]] – Công thức này thay thế khoảng trắng cuối cùng bằng một ký tự ngẫu nhiên ['/' trong trường hợp này, nhưng . Mục đích của việc này là để có thể dễ dàng định vị nó trong chuỗi. Nó trả về chuỗi “The Reaper in the/North”
- FIND[“/”, SUBSTITUTE[A2,” “,”/”, LEN[A2]-LEN[SUBSTITUTE[A2,” “,””]]]] – Giờ đây, tất cả những gì hàm FIND làm là định vị và trả về giá trị . Đây không là gì ngoài vị trí của không gian cuối cùng mà chúng tôi đã thay thế
Khi bạn đã tìm thấy vị trí của khoảng trắng cuối cùng, bạn có thể tiếp tục và thực hiện các thao tác tiếp theo trên chuỗi
Ví dụ: nếu bạn chỉ muốn trích xuất văn bản xuất hiện sau dấu cách cuối cùng, bạn có thể bọc một hàm RIGHT xung quanh kết quả của công thức này, như minh họa bên dưới
=RIGHT[A2,LEN[A2]-B2]
Công thức trên trích xuất các ký tự LEN[A2]-B2 từ bên phải của chuỗi trong ô A2
Ngoài ra, nếu bạn muốn xóa mọi thứ sau dấu cách cuối cùng, bạn chỉ cần thay thế mọi thứ sau dấu cách cuối cùng bằng một chuỗi trống, như sau
=SUBSTITUTE[A2,RIGHT[A2,LEN[A2]-B2],""]
Bạn có thể khái quát hóa thêm công thức để tìm dấu cách cuối cùng thứ n trong một chuỗi đã cho
Ví dụ: để tìm khoảng trắng cuối cùng thứ hai, bạn có thể trừ 1 từ số lượng khoảng trắng trong chuỗi, để công thức của bạn trở thành
=FIND["/", SUBSTITUTE[A2," ","/", LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]-1]]
Tương tự, để tìm khoảng trắng cuối cùng thứ ba, bạn có thể trừ 2 từ số lượng khoảng trắng trong chuỗi, để công thức của bạn trở thành
=FIND["/", SUBSTITUTE[A2," ","/", LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]-2]]
Để khái quát hóa, có thể tìm thấy dấu cách cuối cùng thứ n trong một chuỗi theo công thức
=FIND["/", SUBSTITUTE[A2," ","/", LEN[A2]-LEN[SUBSTITUTE[A2," ",""]]-[n-1]]]
Có khả năng chuỗi văn bản của bạn có dấu cách ở đầu hoặc ở cuối. Trong những trường hợp như vậy, thay vì sử dụng nguyên trạng chuỗi văn bản, bạn có thể gói nó trong hàm TRIM. Vì vậy, nếu chuỗi văn bản nằm trong ô A2, bạn nên sử dụng TRIM[A2] trong công thức, Điều này sẽ đảm bảo rằng các khoảng trắng ở đầu và cuối sẽ tự động bị bỏ qua
Sử dụng Hàm tùy chỉnh VBA để tìm khoảng trắng cuối cùng trong chuỗi trong Excel
Một cách khác để tìm khoảng trắng cuối cùng trong chuỗi là sử dụng hàm tùy chỉnh VBA [còn được gọi là Hàm do người dùng xác định]
Điều này khá tiện lợi vì nó giúp đơn giản hóa công thức của bạn, phần lớn công việc được thực hiện trong định nghĩa hàm
Hơn nữa, bạn có thể lưu chức năng tùy chỉnh này một lần và sử dụng lại nhiều lần. Vì vậy, nó khá hữu ích nếu bạn phải thường xuyên thực hiện các thao tác như thế này
Giả sử bạn có cùng một danh sách các chuỗi và bạn muốn tìm vị trí của khoảng trắng cuối cùng trong mỗi chuỗi
Để tìm không gian cuối cùng, chúng tôi đã tạo một hàm gọi là FindLastSpace và định nghĩa nó như sau
'Code developed by Steve Scott from //spreadsheetplanet.com Function FindLastSpace[cell As Range] Dim rng As Range Dim rLen As Integer rLen = Len[cell] For i = rLen To 1 Step -1 If Mid[cell, i – 1, 1] = ” ” Then FindLastSpace = i – 1 Exit For End If Next i End Function
Hàm này lấy một ô chứa văn bản và trả về vị trí của khoảng trắng cuối cùng ở dạng một số nguyên
Để chức năng này hoạt động, trước tiên chúng ta cần đưa nó vào trình soạn thảo Visual Basic
Dưới đây là cách thêm hàm tùy chỉnh này vào Excel của bạn để bạn có thể sử dụng như một hàm thông thường trong trang tính
- Từ menu chính, điều hướng đến Nhà phát triển->Visual Basic
- Thao tác này sẽ mở cửa sổ VBA, Chọn Chèn-> Mô-đun và bây giờ bạn có thể bắt đầu viết mã.
- Nhập hoặc sao chép-dán đoạn mã trên vào cửa sổ mô-đun
- Chức năng do người dùng xác định của bạn hiện đã sẵn sàng để sử dụng
Trong trang tính Excel của bạn, chỉ cần sử dụng hàm này như cách bạn sử dụng bất kỳ hàm Excel nào khác
Vì vậy, để tìm vị trí của khoảng trắng cuối cùng trong ô A2, bạn có thể nhập công thức sau vào ô B2
=FindLastSpace[A2]
Khi bạn nhấn phím quay lại, bạn sẽ thấy kết quả trong ô B2. Sao chép công thức này xuống các ô còn lại trong cột B [sử dụng núm điều khiển điền]
Đây là kết quả bạn sẽ nhận được
Giải thích về kịch bản
Phần chính của kịch bản nằm trong vài dòng sau
________số 8_______Khối mã này bắt đầu lặp ngược, bắt đầu từ ký tự cuối cùng của chuỗi đã cho
Chúng tôi sử dụng hàm Mid[] để trích xuất một ký tự ở mỗi vòng lặp
Ngay khi vòng lặp gặp ký tự khoảng trắng, nó sẽ trả về vị trí [i -1] của ký tự trong chuỗi cho hàm gọi và dừng vòng lặp
Giờ đây, hàm do người dùng xác định [FindLastSpace] sẽ có sẵn trong tất cả các trang tính của sổ làm việc của bạn
Nếu bạn muốn nó sẵn dùng trong tất cả các sổ làm việc của mình thì bạn có thể lưu nó trong Sổ làm việc Macro Cá nhân của mình hoặc tạo một phần bổ trợ từ nó.
Trong hướng dẫn này, chúng tôi đã chỉ cho bạn hai cách để tìm khoảng trắng cuối cùng trong một chuỗi
Phương pháp đầu tiên sử dụng một công thức phức tạp, trong khi phương pháp thứ hai sử dụng hàm tùy chỉnh VBA mà bạn có thể lưu trong Sổ làm việc Macro Cá nhân của mình và sử dụng bất cứ khi nào cần