The FINDB function in Excel finds where one piece of text starts inside another and returns that position as a number. It works just like the FIND function, except it’s built to count in bytes instead of characters.
That byte counting only matters for Japanese, Chinese, and Korean text, and only when one of those languages is your default editing language in Office. Then each of those characters counts as 2 instead of 1, so FINDB can return a higher position than FIND.
On any other setup, including the English Excel I used for this tutorial, FINDB returns exactly what FIND returns.
In this article, I’ll show you how FINDB works, how to pair it with the LEFTB function to pull out text, and what it actually returns on Japanese text.
Follow along with the example file
FINDB Function Examples.xlsx
FINDB Function Syntax
Here is the syntax of the FINDB function:
=FINDB(find_text,within_text,[start_num])
- find_text is the text you want to find. It can be a single character, a word, or a reference to a cell that has the text.
- within_text is the text you want to search in, usually a cell or a range of cells.
- [start_num] is optional. It’s the position where FINDB starts looking, and if you leave it out, the search starts at position 1.
When you use start_num, FINDB only skips the part of the text before that position. The number it returns is still counted from the first character of the text, not from where the search started.
The start_num value has to be at least 1, and it can’t be larger than the length of the text. A 0, or a number past the end of the text, gives you a #VALUE! error.
If FINDB can’t find find_text anywhere in within_text, it also returns a #VALUE! error.
The FINDB function works in all Excel versions, including 365, 2024, 2021, 2019, 2016, Mac, and the web. The range formulas in this article spill their results, which needs Excel 365, 2024, or 2021.
When to Use the FINDB Function
Use the FINDB function when you need to:
- Find the position of a character or a word inside a text string
- Get a position to feed into the LEFTB, MIDB, or RIGHTB functions so you can pull out part of a text
- Check whether a cell contains some text with the exact same uppercase and lowercase letters
- Work with Japanese, Chinese, or Korean text on a setup where positions are counted in bytes
Let me show you a few practical examples of how to use the FINDB function.
Example 1: Find the Position of a Character with FINDB
Let’s start with a simple example.
Below is a list of movies with the release year in brackets, and I want to find the position of the opening bracket in column B.
Here is the formula:
=FINDB("(",A2:A11)
FINDB looks for the opening bracket in each movie title and returns where it sits. In “Inception (2010)”, the bracket is the 11th character, so the result is 11.
On English text like this, every character counts as one byte, so the byte position and the character position are the same number.
Since I gave FINDB the whole range A2:A11, the formula spills the results down to B11. You only enter it once, in cell B2.
If you are using Excel 2019 or earlier, enter the below formula in cell B2 and then copy it down the range:
=FINDB("(",A2)
The same works for the other spilling formulas in this article. Change the range to its first cell, enter the formula in the first result cell, and copy it down.
Example 2: Start the Search at a Later Position (start_num)
Now let’s use the optional third argument.
Below is a list of product codes made up of a product, a color, and a size separated by hyphens, and I want the position of the second hyphen in column B.
Here is the formula:
=FINDB("-",A2:A11,FINDB("-",A2:A11)+1)
How this formula works:
FINDB("-",A2:A11)finds the first hyphen in each code. For TS-RED-M, that’s position 3.- Adding 1 to it gives 4, so the outer FINDB starts looking right after the first hyphen.
- The outer FINDB then finds the next hyphen, which is at position 7 in TS-RED-M.
Notice that the result is 7 and not 4. Even though the search started at position 4, FINDB still counts the position from the start of the code.
This works because every code here has two hyphens. If a code had only one, the outer FINDB wouldn’t find a second hyphen and would return a #VALUE! error for that row.
Example 3: Case-Sensitive Text Check with FINDB
Here’s another practical scenario.
Below is a list of support notes, and I want to check which ones mention a customer ID, written in capitals, in column B.
Here is the formula:
=ISNUMBER(FINDB("ID",A2:A11))
FINDB returns a number when it finds “ID” in a note and a #VALUE! error when it doesn’t. The ISNUMBER function then turns each result into TRUE or FALSE.
Since FINDB is case-sensitive, it only matches “ID” in capital letters. So “Video call scheduled” returns FALSE, even though the word Video has the letters i and d in it.
To see why that matters, here is the same check with the SEARCHB function in column C. SEARCHB works like FINDB but ignores case:
=ISNUMBER(SEARCHB("ID",A2:A11))
SEARCHB returns TRUE for “Video call scheduled”, “Refund paid”, and “Guide sent by email”. It matches the lowercase “id” inside Video, paid, and Guide, even though none of those notes mention an ID.
FINDB also doesn’t support wildcards, so a ? or * in find_text is treated as a plain character. If you need a case-insensitive search, or you want to use wildcards, use the SEARCHB function instead.
Example 4: Extract Text Before a Space with LEFTB and FINDB
Let’s step it up and use FINDB to pull out part of a text.
Below is a list of full names, and I want to extract the first name of each person in column B.
Here is the formula:
=LEFTB(A2:A11,FINDB(" ",A2:A11)-1)
How this formula works:
FINDB(" ",A2:A11)finds the position of the space in each name. For Henry Phillips, that’s 6.- Subtracting 1 gives 5, which is the length of the first name.
- LEFTB then returns the first 5 bytes from the left of the name, which is Henry.
The formula needs a space in every name. A single-word name would give a #VALUE! error, because FINDB has no space to find.
I’ve used the LEFTB function here instead of the LEFT function on purpose. FINDB returns a position in bytes, and LEFTB counts in bytes too, so the two always agree with each other.
On a Japanese, Chinese, or Korean default setup, Microsoft says each double-byte character counts as 2 bytes. A byte position from FINDB would then no longer match a character count, so feeding it to LEFT could cut the text in the wrong place.
So the simple rule is to keep the pairs together. Use FINDB with the LEFTB, MIDB, and RIGHTB functions, and use FIND with the LEFT, MID, and RIGHT functions.
Pro Tip: If you’re using Excel 365 or 2024, the TEXTBEFORE function does this in one step. The formula =TEXTBEFORE(A2:A11," ") gives the same first names.
Example 5: FINDB vs FIND on Japanese Text
Finally, let’s look at the kind of text FINDB was built for.
Below is a list of Japanese cities, each written in Japanese characters followed by a hyphen and the English name, and I want the position of the hyphen in column B.
Here is the formula:
=FINDB("-",A2:A9)
On my Excel, FINDB returns 3 for 東京-Tokyo. It counts 東 and 京 as one byte each, so the hyphen ends up in position 3.
Now here is the FIND function on the same cities, in column C:
=FIND("-",A2:A9)
FIND returns exactly the same numbers. On this setup, FINDB and FIND behave the same way, even on Japanese text.
This happens because FINDB only counts a double-byte character as 2 bytes when Japanese, Chinese, or Korean is enabled for editing and set as the default language in Office.
My Excel uses English as the default editing language, so FINDB counts every character as 1.
According to Microsoft’s documentation, with Japanese set as the default editing language, 東 and 京 would count as 2 bytes each. FINDB would then put the hyphen at position 5, while FIND would still return 3.
I haven’t shown that result here because my Excel isn’t set up that way.
Important: FINDB’s results depend on the default editing language of the computer that opens the file. If you share a workbook with someone whose default language is Japanese, Chinese, or Korean, the same FINDB formula can return different numbers for them.
If you need the position in characters no matter whose computer opens the file, use the FIND function, which always counts each character as 1.
So that’s how the FINDB function works. On most setups it gives the same result as FIND, and its byte counting only kicks in when Japanese, Chinese, or Korean is the default editing language.
If you do use FINDB, pair it with LEFTB, MIDB, and RIGHTB so the positions always line up. I hope you found this tutorial useful.
Other Excel Articles You May Also Like:
