- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.
This post will teach you how to use the FIND and FINDB functions in Microsoft Excel. You will learn how they work, the syntax of each respective function, and also how to use both of them with some examples.
Description of FIND and FINDB functions
FIND and FINDB operate by finding one text string inside a second text string, and yield the amount of the beginning position of the initial text string from the first character of the second text string.
- These functions might only be found in some languages.
- FIND is solely designated to be used with languages that employ the single-byte character set (SBCS), unlike FINDB which is tailored for usage with languages that operate the double-byte character set (DBCS). The standard language setting on your computer determines the return value in this exact way:
- FIND invariably counts each character, whether single-byte or double-byte, as 1, regardless of what the automatic language setting is.
- FINDB counts each double-byte character as 2 after you have activated the language editing that allows DBCS and then format it as the standard language. Or else, FINDB tallies each character as 1.
The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.
Syntax of FIND and FINDB Functions
FIND(find_text, within_text, [start_num])
FINDB(find_text, within_text, [start_num])
The FIND and FINDB function syntax contains these listed arguments:
- Find_text – Required. The text you are searching for.
- Within_text – Required. The text including the target text that you are looking for.
- Start_num – Optional. Confirms the character at which to begin the search. The initial character in within_text is character number 1. If you skip start_num, it is presumed to be 1.
- FIND and FINDB are case-sensitive and prohibit wildcard characters. If you want to avoid doing a case-sensitive search or using wildcard characters, alternatively you can employ SEARCH and SEARCHB.
- If find_text is “” (empty text), FIND corresponds to the first character in the search string (that is, the character numbered start_num or 1).
- Find_text does not accept any wildcard characters.
- If find_text is absent from within_text, FIND and FINDB capture the #VALUE! error value.
- If start_num is lesser than zero, FIND and FINDB return the #VALUE! error value.
- If start_num is higher than the length of within_text, FIND and FINDB yield the #VALUE! error value.
- Use start_num to omit a defined number of characters. Applying FIND as an example, imagine you are working with the text string “AYF0093.YoungMensApparel”. To find the number of the first “Y” in the descriptive part of the text string, define start_num equal to 8 so that the serial-number portion of the text is skipped. FIND starts with character 8, finds find_text at the following character, and returns the number 9. FIND constantly returns the number of characters from the outset of within_text, counting the characters you ignore if start_num is higher than 1.
Examples of FIND and FINDB
Copy the sample data in the below table, and paste it into cell A1 of a new Excel worksheet. For formulas to indicate results, choose them, press F2, and then press Enter. If you must at any time, you can modify the column widths to view all the data in all its wonder.
|=FIND(“M”,A2)||Position of the first “M” in cell A2||1|
|=FIND(“m”,A2)||Position of the first “M” in cell A2||6|
|=FIND(“M”,A2,3)||Position of the first “M” in cell A2, starting with the third character||8|
|Ceramic Insulators #124-TD45-87|
|Copper Coils #12-671-6772|
|Variable Resistors #116010|
|=MID(A2,1,FIND(” #”,A2,1)-1)||Extracts text from position 1 to the position of “#” in cell A2 (Ceramic Insulators)||Ceramic Insulators|
|=MID(A3,1,FIND(” #”,A3,1)-1)||Extracts text from position 1 to the position of “#” in cell A3 (Copper Coils)||Copper Coils|
|=MID(A4,1,FIND(” #”,A4,1)-1)||Extracts text from position 1 to the position of “#” in cell A4 (Variable Resistors)||Variable Resistors|
Similar Topics to Check Out
Expand your Office skills
Get instant Excel help
Subject to Got It terms and conditions.