Text functions in Excel are very helpful for extracting a part of a text.

In the following examples, you will see in many examples how to extract a part of a cell

The LEFT function

We want to extract the dialing code of a phone number. The dialing code is the first 3 digits of the phone number. To extract these figures, we will use the function LEFT.

This function is very simple to use because you only need 2 pieces of information:

1. your initial text (generally the reference of a cell)
2. the number of digits you want to extract (3 in this case).

The function has the following writing:

=LEFT(B2,3)

RIGHT function

In the same way, if you want to extract the last 4 digits of the phone number, you use this time the function RIGHTas follow:

=RIGHT(B2,4)

MID function

It is also possible to extract a part of a string in the middle. To do that, you will use the MID function.

To write this function, you must

• First, put the reference of the cell
• Second, indicate the position where you want to start the extraction (in our example, position 9)
• Third, indicate the number of characters you want to extract (8 characters to return)

So, if you want to extract the mobile phone number of the cell B8 in the cell C8, you will write the following formula:

 =MID(B2,9,8)

In this example

• 20 represents the number of characters to start
• 8 represents the phone number (7 digits + the dash)

Create a more complex extract

These 3 functions are very important to extracting part of a string. But you can also work with other functions to extract more complex sub-string.

• LEN counts the number of characters in a string
• SEARCH helps to find a specific character in a string
• TRIM cleans your string of the unexpected blank

Extract text without formula

Since Excel 2013 or the newest version of Excel, you can extract text without formula. This tool is the FlashFill

FlashFill performs the extraction on the fly. If your data are refreshed, the extract will not be refreshed 🤨😕