Pullng data out of string of text

K

Kennedy

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.
 
J

Jacob Skaria

'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))
 
L

Luke M

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))
 
D

Diabolo_Devil

Try:

Data Tab > Data Tools Section > Text to Columns > Delimited option > Comma
Delimiter chosen > Choose detination of data > Finish
 
K

Kennedy

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! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top