Excel formula wanted

Chris the Ancient 13:40 26 Aug 05
Locked

Here's one to tax the good ol' brain cells!

I'm trying to do a little bit of manipulation of my contact emails - it seems to have grown.

What I would like to achieve is a formula where I can split the email address into three parts, the name, the @ and the isp. Then I can do some sorting on it.

While I can do some string manipulation in formulas, I haven't cracked how to make the split into three columns!

TIA

CtA

xania 14:20 26 Aug 05

I don't know of any way to do this in Excel but here's a thought. Export the data to a .csv file and use find and replace to replace <@> with <,@,>. You can then import the result into a new spreadsheet.

VoG II 15:15 26 Aug 05

Data | Text to Columns ?

VoG II 15:43 26 Aug 05

Name: =LEFT(C11,FIND("@",C11)-1)

@: =MID(C11,FIND("@",C11),1)

ISP: =RIGHT(C11,LEN(C11)-FIND("@",C11))

Monoux 15:48 26 Aug 05

E mail me using yellow envelope and I'll send you a speadsheet with a control button that will do want you want. You will need to edit the code behind the button to increase from 4 the number of rows it is set up for

Simsy 15:48 26 Aug 05

I'm going to split it into 2 parts, the bit before the "@", and the bit after, as the "@" is constant you can add this wherever as appropriate;

Suppose the email address is in cell A1, have the following fomula in B1, and it will give the bit before the "@"

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

I'll explain this, then do the bit after the "@"...

LEFT, means show the "left most" characters in the cell in question, A1 in this case...

=LEFT(A1,

Then after the comma, we have to put how many characters we want to show. In this case it's variable, depending on how many characters are before the "@", so we have a formula to find out what position the "@" is in...

FIND("@",A1,1)

The above says, "find "@" in A1, starting at the first character, and return what position it's in". That is, if the "@" is the 7th character it will give the answer 7

Now, if the answer is 7 we want the first 6 characters, hence the "-1"

I hope that makes sense...

In cell C1 the following formula will give what comes after the "@"

=RIGHT(A1,LEN(A1)-FIND("@",A1,1))

I'll leave you to work it out!

I hope this helps!

Regards,

Simsy

Monoux 15:49 26 Aug 05

E mail me using yellow envelope and I'll send you a speadsheet with a control button that will do want you want. You will need to edit the code behind the button to increase from 4 the number of rows it is set up for

Simsy 15:54 26 Aug 05

I thought I might have beaten VoG™ to a formula answer...

In fact I probably would've had not my employment interrupted me twice!!

Regards,

Simsy

Monoux 16:17 26 Aug 05

Simsy

You'll have to be like greased lightening to do that!!

Chris the Ancient 20:22 26 Aug 05

Sorry for the delay, my need to earn a bit of cash reared it's head.

I had to wonder what took VoG™ so long! Nearly two hours!

VoG™ - as always when I struggle, you're a star. Many thanks.

Simsy - You nearly beat him! And both answers the same. Well done and many thanks

Monoux - Many thanks for the kind offer, and a good one. Not being a grinch, but I will pass as I can use the formula - and it will be a strictly one off exercise. But I appreciate it and offer my thanks to you as well.

I shall tick it and then go and do the deed.

CtA

VoG II 20:35 26 Aug 05

My excuse is that I was painting my kitchen. Finished that and logged on briefly to post my Text to Columns suggestion (which does work btw if you select @ as the separator).

Followed by a cleanup of the working area and a bath to remove accumulated debris from me before I could post the formulas.

Incidentally, the syntax for the FIND function seems to me to be the reverse of that in related functions.

This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch