• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to strip off first name and last name from a string that has email address, @

No, this separates the string after @. I have done this part.
I want to separate first name and last name?
 
Hi GN0001,

Are the characters between First name & last name fixed? Are you going to use these 3 only, "-","."," " as a seperator between first name and last name? If yes than you can use below formula for first Name

=MID(MID(E2,1,SEARCH("@",E2)-1),1,MAX(IFERROR(SEARCH({"-","."," "},MID(E2,1,SEARCH("@",E2)-1))-1,0)))

and for last name:

=MID(MID(E2,1,SEARCH("@",E2)-1),1,MAX(IFERROR(SEARCH({"-","."," "},MID(E2,1,SEARCH("@",E2)-1))-1,0)))

E-mail id is E2.

Please note that these are array formula, so must be entered with Ctrl+Shift+Enter.



Regards,
 
Last edited:
May be a more general solution.

For First Name:
=MID(MID($E2,1,SEARCH("@",$E2)-1),1,MATCH(1,--(CODE(MID(LOWER(MID($E2,1,SEARCH("@",$E2)-1)),ROW(INDIRECT("1:"&LEN(MID($E2,1,SEARCH("@",$E2)-1)))),1))<97),0)-1)

For Last Name:
=MID(MID($E2,1,SEARCH("@",$E2)-1),MATCH(1,--(CODE(MID(LOWER(MID($E2,1,SEARCH("@",$E2)-1)),ROW(INDIRECT("1:"&LEN(MID($E2,1,SEARCH("@",$E2)-1)))),1))<97),0)+1,255)

Both entered as array formula.

Regards,
 
Somendra Misra said:
May be a more general solution.

For First Name:
=MID(MID($E2,1,SEARCH("@",$E2)-1),1,MATCH(1,--(CODE(MID(LOWER(MID($E2,1,SEARCH("@",$E2)-1)),ROW(INDIRECT("1:"&LEN(MID($E2,1,SEARCH("@",$E2)-1)))),1))<97),0)-1)

For Last Name:
=MID(MID($E2,1,SEARCH("@",$E2)-1),MATCH(1,--(CODE(MID(LOWER(MID($E2,1,SEARCH("@",$E2)-1)),ROW(INDIRECT("1:"&LEN(MID($E2,1,SEARCH("@",$E2)-1)))),1))<97),0)+1,255)

Both entered as array formula.

Regards,
Click to expand...
Thank you very much for the answer provided.
I might have more questions on this.
GN0001
 
You must log in or register to reply here.
Back
Top