Pullng data out of string of text

  • Thread starter Thread starter Kennedy
  • Start date Start date
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.
 
'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))
 
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))
 
Try:

Data Tab > Data Tools Section > Text to Columns > Delimited option > Comma
Delimiter chosen > Choose detination of data > Finish
 
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! :-)
 
Back
Top