27. Excel How to extract text from a cell ?


An example relating to the extraction of first and last names from full names is given in the form of an exercise: Text Functions.

I give here as an example the extraction of identifiers from Email addresses.

An email address is composed of an identifier followed by the @ character and a domain name.

Excel 2016 Example Extracting text from a cell

Extraction of the identifier of an email address

To extract the identifier enter the following formula:

=LEFT(B2,FIND("@",B2)-1)

Or

=MID(B2,1,FIND("@",B2)-1)

Explanation :

The function LEFT returns a part of the text given in the 1st argument starting from the beginning of this text. The number of characters in the returned text is specified in the 2nd argument.

The number of characters for the identifier of an email address is equal to the position of the "@" character in that address minus 1.

The MID function also returns part of the text given in the 1st argument starting at the position specified in the 2nd argument. The number of characters in the returned text is specified in the 3rd argument.

Extracting the domain name from an email address

If you want to extract the domain name, use the :

=RIGHT(B2,LEN(B2)-FIND("@",B2))

Or

=MID(B2,FIND("@",B2)+1,LEN(B2)-FIND("@",B2))

The function RIGHT returns a part of the text given in the 1st argument. The characters are extracted from the end of the text and the number of characters is given as the 2nd argument.

The number of characters for the domain name of an email address is equal to the number of characters in the text of that address minus the position of the "@" character.

If I use the MID function, then I have to specify the starting position for character extraction as the second argument. This position for our case is equal to the position of the "@" character plus 1.


<< 26. Excel How to Delete Filtered Rows ?

28. Excel How to edit a drop-down list ? >>