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.

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 1^st argument starting from the beginning of this text. The number of characters in the returned text is specified in the 2^nd 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 1^st argument starting at the position specified in the 2^nd argument. The number of characters in the returned text is specified in the 3^rd 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 1^st argument. The characters are extracted from the end of the text and the number of characters is given as the 2^nd 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.

---