text to columbs formula

[cyote101]

hello folks, I've searched the help files and could not locate what i'm looking for.

i have a cell with ":Manager:California:Soap:Jerry Harding"

now what i'm looking for is a formula that can lookup the 2nd value in the cell the one after the secound colen and before the third colen. so for this example we want to lookup the secound value so "California" is reterned

I would like to be able to change a value in the formula so it would lookup the 4th or 26th and so on, value in the Cell.

Thanks

 

[Jonmo1]

If this is a one time thing, used for 1 column and always the value between the 2nd and 3rd colon, this works...

=MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,(FIND(":",A1,(FIND(":",A1,FIND(":",A1)+1))+1)-FIND(":",A1,FIND(":",A1)+1))-1)

Don't ask how it works, I already forgot.
I'm sure somebody else has something better. LOL.

 

[Von Pookie]

A terrible, terrible, long formula, but should work as long as the first item in the list is *always* preceeded by a colon:

Book1
	A	B	C	D
1	What value would you like to find (enter number)	2
2
3	:Manager:California:Soap:Jerry Harding	California
Sheet1

Formula in B3 is:
=IF(OR($B$1<=0,$B$1>(LEN($A3)-LEN(SUBSTITUTE($A3,":","")))),"",IF($B$1=(LEN($A3)-LEN(SUBSTITUTE($A3,":",""))),MID($A3,FIND("@",SUBSTITUTE($A3,":","@",$B$1))+1,255),MID($A3,FIND("@",SUBSTITUTE($A3,":","@",$B$1))+1,(FIND("@",SUBSTITUTE($A3,":","@",$B$1+1))-FIND("@",SUBSTITUTE($A3,":","@",$B$1)))-1)))

I went with entering the value in a cell so it can be easily changed. Just enter the value you want from the string (1,2,3, etc.) in B1 and it should grab it.

 

[Richard Schollar]

Hi

Here's an alternative:

=TRIM(LEFT(SUBSTITUTE(REPLACE(D26,1,FIND("@",SUBSTITUTE(D26,":","@",3)),""),":",REPT(" ",100),1),100))

you need to replace the bolded 3 with whatever instance number of ":" in he string you want your word to start at.

 

[Von Pookie]

Heh. I knew there was a better way than my monstrosity. I would have never thought of that, though. Have to (try to) keep that little trick in mind...

 

[cyote101]

Thanks, I would never have even come even close!!!!!

You guys rock, This is very usefull for me. Thanks

ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: