MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Splitting The Contents of a Cell

April 16, 2002 - by Bill Jelen

Ivo asks:

I was wondering how to separate a column with in to columns [email] and []

There are a couple functions you will use to do this.

=FIND("@",A2) will tell you which position contains the @ in a cell. In your example, this would return position 6.

You can then use =LEFT(A2,FIND("@",A2)-1) to return email and =MID(A2,FIND("@",A2),LEN(A2)+1-FIND("@",A2)) to return


Juan Pablo just pointed out that you could do this easily with Data > Text to Columns. Choose Delimited text and make the delimiter be the @ sign.

Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.