How to parse email address to columns

[Sir Vili]

Hello everybody.

Problem:

How to parse email address to columns with formulas?

Example (imaginary):
In A1 there is kit.carson@wildwest.com

I want in B1 kit; C1 carson; D1 wildwest That D1 must be without .com

Sir Vili.

 

[Iain Lewis]

Hi Sir Vili,

These formulas should work

Cell B1
=LEFT(A1,SEARCH(".",A1,1)-1)

Cell C1
=MID(A1,SEARCH(".",A1,1)+1,((SEARCH("@",A1,1)-1)-SEARCH(".",A1,1)))

Cell D1
=MID(A1,SEARCH("@",A1,1)+1,SEARCH(".",A1,SEARCH("@",A1,1))-1-SEARCH("@",A1,1))

Please note - I have only tested them on the email format you have supplied. If you have any other formats ( e.g. name@company.com ; first.last@site.company.co.uk etc ) then you will need other formulas. If there are a lot of different amila address ormats then I'd suggest using a VB script to check the format & then apply the correct sets of formulas.

HTH,

Iain

 

[Sir Vili]

Hello Iain.

I appreciate Your solution. These formulas suits for my use at the moment.

Thank You very much.

Best Regards Sir Vili.

 

[Tom@CPC]

Sir Vili,

Something else you could do, a 2 step process manually, is to select your cell with the e-mail address. From there select Text to Columns from the data menu. Type in a decimal (.) in the "other" delimiter field. You can change the destination to the cell beside the original if you want to maintain the source. This will parse in to (3 in this case) columns.

Repeat the process and change the delimiter to the @ sign. In my try, i left the destination as the same cell and overwrote the cell containing the com entry as you indicated you did not want the com in the output.

If you recorded a macro to perform both steps, you could automate the process greatly and have it available at any time.

HTH, Regards
Tom

 

[Aladin Akyurek]

On 2002-09-06 00:57, Sir Vili wrote:
Hello everybody.

Problem:

How to parse email address to columns with formulas?

Example (imaginary):
In A1 there is kit.carson@wildwest.com

I want in B1 kit; C1 carson; D1 wildwest That D1 must be without .com

Sir Vili.

If you installed the great morefunc add-in:

In B1 enter:

=WMID(A1,1,1,".")

In C1 enter:

=WMID(SUBSTITUTE(A1,"@","."),2,1,".")

In D1 enter:

=WMID(SUBSTITUTE(A1,"@","."),3,1,".")

 

[Sir Vili]

Hi Tom@CPC.

Excellent idea. Worked fine. Thank You very much.

Hi Aladin.

I checked now in my home Your formulas and found then easy to use. Thank You again helping me and giving varieties to solve problems with formulas. I like it.

Best regards to both.
Sir Vili.