Pullng data out of string of text
 

Trying to find a way to pull data out of a string of text separated by comas.
Currently using a MID function, but unfortunately the number of characters
are not consistent.
Using =MID(K50,11,8) to pull back data in column K50, 11 spaces in, 8
characters long.
What I need is to be able to find data between a coma. So if the data in the
column looks like San Diego,02/12/2010,Hilton Hotel...I want to be able to
find only the date out of the string of text, or any data in between the
comas.
Thanks for any input.

Pullng data out of string of text
 

'If the text string to be extracted is always 10 digits long then
=MID(A1,FIND(",",A1)+1,10)

'If the string is variable lenght
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),2),FIND(",",A1)+1,255))


--
Jacob


"Kennedy" wrote:

Trying to find a way to pull data out of a string of text separated by comas.
Currently using a MID function, but unfortunately the number of characters
are not consistent.
Using =MID(K50,11,8) to pull back data in column K50, 11 spaces in, 8
characters long.
What I need is to be able to find data between a coma. So if the data in the
column looks like San Diego,02/12/2010,Hilton Hotel...I want to be able to
find only the date out of the string of text, or any data in between the
comas.
Thanks for any input.

Pullng data out of string of text
 

Can you use:
=DATEVALUE(MID(K50,FIND(",",K50)+1,10))

A more robust formula, in case date format changes:
=DATEVALUE(LEFT(MID(K50,FIND(",",K50)+1,999),FIND( ",",MID(K50,FIND(",",K50)+1,999))-1))

--
Best Regards,

Luke M
"Kennedy" wrote in message
...
Trying to find a way to pull data out of a string of text separated by
comas.
Currently using a MID function, but unfortunately the number of characters
are not consistent.
Using =MID(K50,11,8) to pull back data in column K50, 11 spaces in, 8
characters long.
What I need is to be able to find data between a coma. So if the data in
the
column looks like San Diego,02/12/2010,Hilton Hotel...I want to be able to
find only the date out of the string of text, or any data in between the
comas.
Thanks for any input.

Pullng data out of string of text
 

Wow! That's all I can say Jacob. The second one worked, since the string is
variable in length. Don't know what it does, but hey, it works! :-)

"Jacob Skaria" wrote:

'If the text string to be extracted is always 10 digits long then
=MID(A1,FIND(",",A1)+1,10)

'If the string is variable lenght
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),2),FIND(",",A1)+1,255))


--
Jacob


"Kennedy" wrote:

Trying to find a way to pull data out of a string of text separated by comas.
Currently using a MID function, but unfortunately the number of characters
are not consistent.
Using =MID(K50,11,8) to pull back data in column K50, 11 spaces in, 8
characters long.
What I need is to be able to find data between a coma. So if the data in the
column looks like San Diego,02/12/2010,Hilton Hotel...I want to be able to
find only the date out of the string of text, or any data in between the
comas.
Thanks for any input.