Hello All,
I'm trying to extract email address info in the format [email protected] into separate columns for the fname and lname.
I've tried using some FIND nested among MID constructs without much success.
In essence, I want to end up with two additional columns for fname and lname extracted from the email address.
Any help is greatly appreciated.
tks,
Hi,
How about using Data tab---> Text to Columns --> Delimited --> separate by Other (.)
This will separate the first name.
To extract the last name from the domain you can use the following formulawhere 6 represents the number of symbols in the domain name +"@" characterFormula:
Please Login or Register to view this content.
Liked the answer given? click * to say so
Assuming Column A = email
Column B = MID(A1,1,FIND(".",A1)-1)
Column c = MID(A1,FIND(".",A1)+1,FIND("@",A1)-1)
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thanks for the replies gents.
Daffodil11, your first entry for Column B works as desired, however, the column C returns the lastname and "@" and part of the domain name as well.
For example, [email protected] returned flintstone@ston in column C. If I can get rid of the "@ston" I am golden.
tks,
Whoops!
MID(what, start, how far)
MID(A1,start at . + 1, go on for (characters until @ + 1) - (characters until . )
Column C = =MID(A1,FIND(".",A1)+1,FIND("@",A1)-1-FIND(".",A1))
Sorry I left out the subtraction.
Daffodil11, thanks so much.
Works perfectly!
tks,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks