How to reverse engineer an email address in Excel ?

[Lewis_Brown]

Here is a fun question that I need some help with please.

 

I have been asked to find a way to split an existing email address down into its component parts, using Excel.

e.g

A1 = [email protected]

B1 = m

C1 = mouse

D1 = 10

E1 = @school.co.uk

I can create a new email address form the individual parts - just not break one up into its parts.

Any help will be most greatly received.

[ZeroHour]

VB script with a Regex?

https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

[howartp]

Left, right, mid and search() are your functions.

 

I do it regularly to turn Surname,Forename info Forename Surname etc etc etc

[Ditto]

I'm assuming your format is @ You'll need to check this but:

 

In I1 put this formula: =LEFT(A1,FIND("@",A1,1)-1) #this is just to make the rest simpler

then

B1 formula is =LEFT(I1,1)

C1 formula is =MID(I1,2,LEN(I1)-3)

D1 formula is =RIGHT(I1,2)

E1 formula is =RIGHT(A1,LEN(A1)-LEN(I1))

 

No warranty given!

[howartp]

Exactly the right concept; I’ve not checked it either though!

[Bedders]

There's another feature that's very useful, perhaps not in your case but it might be an additional help. I'm in Office 2013 at the moment but the process should be similar for 2007 upwards. It will only let you split on one criteria at a time.

 

Select cells to be split (that contain the full email addresses)

Choose the Data tab at the top

Click on Text to Columns

Select Delimited

Select other and enter the @ symbol into the box

Choose the destination cell for the data (the top-left cell)

 

This process can be followed (fixed instead of Delimited to get the first letter, split the domain cell on the period (.) to get the domain name and TLD etc.)

Edited May 17, 2019 by Bedders

[Lewis_Brown]

So far, using @ I have;

 

=LEFT(A1) that will report

 

=LEFT(A1, LEN(A1)-17) that will report &

 

=RIGHT(A1,LEN(A1)-FIND("@",A1)) that will report

 

 

But I don't have anything that will report just or just

Edited May 17, 2019 by Lewis_Brown

[Ditto]

Yes, nice. I like the simplicity of data splitting on the "@".

[Ditto]

So far, using @ I have;

=LEFT(A1) that will report

=LEFT(A1, LEN(A1)-17) that will report &

=RIGHT(A1,LEN(A1)-FIND("@",A1)) that will report

But I don't have anything that will report just or just

Try this example.

EmailSplit.xlsx

[Lewis_Brown]

Reverse engineering the user details from an email address

[TABLE=align: left]

[TR]

[TD]A

[/TD]

[TD]B

[/TD]

[TD]C

[/TD]

[TD]D

[/TD]

[TD]E

[/TD]

[TD]F

[/TD]

[/TR]

[TR]

[TD]Email

[/TD]

[TD]First Initial

[/TD]

[TD]Surname

[/TD]

[TD]Year Group

[/TD]

[TD]Domain

[/TD]

[TD]Username

[/TD]

[/TR]

[TR]

[TD][email protected]

[/TD]

[TD]m

[/TD]

[TD]mouse

[/TD]

[TD]10

[/TD]

[TD]@school.org.uk

[/TD]

[TD]mmouse10

[/TD]

[/TR]

[/TABLE]

 

 

 

 

First;

F2 =LEFT(A2,FIND("@",A2,1)-1)

 

Then;

 

B2 =LEFT(F2,1)

C2 =MID(F2,2,LEN(F2)-3)

D2 =RIGHT(F2,2)

E2 =RIGHT(A2,LEN(A2)-LEN(F2))

[Lewis_Brown]

A huge thanks to ZeroHour, howartp, Ditto & Bedders for their input on this one.