Extract string from cell

  • Thread starter Thread starter G. I.
  • Start date Start date
If it is always the third # that is preceding the email and there is always
a # |\ at the end this will work

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"#","^^",LEN(A1)-LEN(SUBSTI
TUTE(A1,"#",""))-1))),FIND("#",RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"#
","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))-1))))-1)


of course there can't be any ^^ in the strings
 
In first column use this =FIND("@",A2,1)
In second column use this =FIND("#",A2,B2-8)
Third column use this =B2-C2-1
Forth use this =MID(A2,C2+1,D2)


----- G. I. wrote: -----

I have a column of data as follows:

JOHN SMITH#INTERN#3154#[email protected]# | JANE DOE#SPECIALIST#2312#[email protected]# | .
 
Try using the Find( function. It will return the position of a certain character in the string. Looks like you'll be searching for the 3rd "#". Here is the formula I came up with. It's kind of long though...
=MID(A1,FIND("#",A1,FIND("#",A1,FIND("#",A1)+1)+1)+1,FIND("#",A1,FIND("#",A1,FIND("#",A1,FIND("#",A1)+1)+1)+1)-FIND("#",A1,FIND("#",A1,FIND("#",A1)+1)+1)-1)

Where A1 is the string you're searching in.
 
Hi GI!

Would this work?

=LOWER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,FIND("#",A1)-1-FIND(" ",A1)))

You could get the jsmith and jdoe out but if it's regular as shown,
this approach will be shorter.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Re: Peo Sjoblom

Thanks for your help!
-----Original Message-----
If it is always the third # that is preceding the email and there is always
a # |\ at the end this will work

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE (A1,"#","^^",LEN(A1)-LEN(SUBSTI
("^^",SUBSTITUTE(A1,"#
","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))-1))))-1)


of course there can't be any ^^ in the strings
--

Regards,

Peo Sjoblom




.
Click to expand...
 
If it is always the third # that is preceding the email and there is always
a # |\ at the end this will work

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"#","^^",LEN(A1)-LEN(SUBSTI
TUTE(A1,"#",""))-1))),FIND("#",RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"#
","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))-1))))-1)

of course there can't be any ^^ in the strings
Click to expand...
...

Your formula assumes the e-mail address lies between the penultimate and final
"#" in the string rather than after the 3rd one, and it doesn't depend on the
ending characters other than the existence of a "#" after the e-mail address.

If there could be a ^^ in the string, then one alternative would be defining a
name like Seq referring to =ROW(INDIRECT("1:1024")) and using the array formula

=MID(A1&"#",SMALL(IF(MID(A1,Seq,1)="#",Seq),3)+1,
SMALL(IF(MID(A1&"#",Seq,1)="#",Seq),4)-SMALL(IF(MID(A1,Seq,1)="#",Seq),3)-1)

This generalizes more easily for an arbitrary field seperator string, SEP, as

=MID(A1&SEP,SMALL(IF(MID(A1,Seq,1)=SEP,Seq),3)+LEN(SEP),
SMALL(IF(MID(A1&SEP,Seq,1)=SEP,Seq),4)-SMALL(IF(MID(A1,Seq,1)=SEP,Seq),3)
-LEN(SEP))
 
Back
Top