How to Find text in a cell in Excel

The FIND function in Excel delivers the numerical location of one text string inside another. The #VALUE error is returned by FIND if the text cannot be found.

The Nested Formula Construction to find text in a cell in Excel:

We'll utilise a nested formula with the LEFT and FIND functions to find text in a cell in Excel for the first example. We'll keep adding new sets of parenthesis as we go. We will utilise one function (FIND) as a parameter for another function (such as LEFT or RIGHT) in a nested structure.

Starting with =FIND ( ,C2). In plain language, our function syntax instructs Excel to search for a blank space in reference cell C2, which is denoted by the  in our example. Although I inserted 1 as the starting position in the image below, this parameter is optional, and Excel starts at 1 by default. Position 5 of cell D2 at position 5 is where Excel discovered the empty space.

Since Excel starts at 1, I'll omit the beginning parameter, which is optional, to make the formula simpler.

Let's now include the LEFT function, making our formula =LEFT(C2,(FIND( ,C2))). We are once again utilising cell C2 in this situation, but the LEFT function will take the contents of cell C2 from positions 1 to 5, which is where the Excel FIND function discovered the empty space.

There is a tiny problem, though. There is a trailing gap in D2 even if you cannot see it. We can observe that cell D2 has 5 characters using the LEN function.

The answer is to remove 1 or utilise the TRIM function, as I mentioned in the Excel name separation tutorial. I'll use -1 for simplicity's sake. Although the visual effects remain the same, the character count has decreased by 1.

How to Use the LEFT Function to find text in a cell in Excel:

You may use the sample spreadsheet in the Resources section or import your data into Microsoft Excel.

Enter Number in cell D1.

Fill in the street name in cell E1

Excel formula =LEFT(C2,(FIND( ,C2)-1)) in cell D2, then hit Enter. In D2, the number 1001 ought to appear.

The following step entails applying this formula to the remaining inputs. However, for the remaining rows, we must not use C2 but rather the appropriate street cell.

To choose the start of our range, click cell D2.

Click and drag the mouse to the bottom right corner.

Copy a formula by clicking the fill handle.

Down the column, paste the Excel formula.

Right below the + cursor, double-click. It will duplicate your formula down the end of the data and you will see the extracted numbers in Column D

How to Use the RIGHT Function to find text in a cell in Excel:

Now, using the RIGHT function, we'll construct a similar nested formula to record the street address. This time, we'll take the material in the Street column that is immediately to the right of the first slot.

Enter the following formula in cell E2: =RIGHT(C2,LEN(C2)-FIND( ,C2)

Enter the key. Ferry Nagar should appear as E2.

To choose the start of our range, click cell E2.

Click and drag the mouse to the bottom right corner.

Right below the + cursor, double-click. Your formula will be copied along the column as a result.

The extracted data from your original street address should appear in columns D and E.

The spreadsheet you create should like the one below.

Did you learn about how to construct formula to find text in a cell in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience