Picking up dates from within a string

S

Sue Compelling

Hi

I have a report that is populated from a data dump from our work management
system. One of the cells will say something like below and will always
contain a date after the word DUE...

VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09

I want to be able to selct only the date (which somethimes will only read
4/11 - I tried the following formula (from this forum) though am coming
unstuck ...

=RIGHT(R2,FIND("DUE",R2&"DUE"))

as it returns the following when run across the above string ...

26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09

TIA ...
 
J

Jacob Skaria

'Extract the date part alone from the string as text string...
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255)," ",REPT(" ",12)),12))

'Extract the date and time. Format the formula cell to excel date
format...The result will be dependent on your system date format.
=--TRIM(MID(A1,FIND(" DUE ",A1)+5,255))


If this post helps click Yes
 
J

Jacob Skaria

To extract the month/day part of date try

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",REPT(" ",10),2),10))

If this post helps click Yes
 
S

Sue Compelling

Jacob - that's brilliant -

You make excel sing!!!

Tell me - if the user has only entered 5/11 as the date - in extracting it -
how do I add on the current year so that it reads 5/11/2009?

Cheers
 
J

Jacob Skaria

Thanks for the feedback. Try the below

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",
REPT(" ",10),2),10)) &"/" & YEAR(TODAY())

If this post helps click Yes
 
S

Sue Compelling

Thanks Jacob - didn't quite return the right result and I'll try and nut it
out ...

When I got your formula I wondered how on earth it did what it did - and
then I broke it right down in to its' component parts. I feel very proud
that I actually got what each step was acheiving - thanks for sharing this
with me - magic.
 
J

John_John

Try this sue:

=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIMEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))
 
M

Mohammad Rahman

You guys are awesome.

Please help me with this, i need to get the date out of this file name

C:\Documents and Settings\Rez\Desktop\Excel forum\Queue Performance Phone Daily15_Dec_2009.csv


If you let me know how this works, i will be to do this myself in the future.

cheers,
Mo



John_John wrote:

Try this
05-Nov-09

Try this sue:

=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIMEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))

Previous Posts In This Thread:

Picking up dates from within a string
Hi

I have a report that is populated from a data dump from our work management
system. One of the cells will say something like below and will always
contain a date after the word DUE...

VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09

I want to be able to selct only the date (which somethimes will only read
4/11 - I tried the following formula (from this forum) though am coming
unstuck ...

=RIGHT(R2,FIND("DUE",R2&"DUE"))

as it returns the following when run across the above string ...

26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09

TIA ...
--
Sue Compelling

Try this...
Try this...

=INT(MID(A1,SEARCH("Due",A1)+4,20))

Format as Date

--
Biff
Microsoft Excel MVP

'Extract the date part alone from the string as text string...
'Extract the date part alone from the string as text string...
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255)," ",REPT(" ",12)),12))

'Extract the date and time. Format the formula cell to excel date
format...The result will be dependent on your system date format.
=--TRIM(MID(A1,FIND(" DUE ",A1)+5,255))


If this post helps click Yes
---------------
Jacob Skaria


:

To extract the month/day part of date try=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("
To extract the month/day part of date try

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",REPT(" ",10),2),10))

If this post helps click Yes
---------------
Jacob Skaria


:

Jacob - that is brilliant -You make excel sing!!!
Jacob - that is brilliant -

You make excel sing!!!

Tell me - if the user has only entered 5/11 as the date - in extracting it -
how do I add on the current year so that it reads 5/11/2009?

Cheers
--
Sue Compelling


:

Thanks for the feedback.
Thanks for the feedback. Try the below

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" DUE ",A1)+5,255),"/",
REPT(" ",10),2),10)) &"/" & YEAR(TODAY())

If this post helps click Yes
---------------
Jacob Skaria


:

Thanks Jacob - did not quite return the right result and I will try and nut
Thanks Jacob - did not quite return the right result and I will try and nut it
out ...

When I got your formula I wondered how on earth it did what it did - and
then I broke it right down in to its' component parts. I feel very proud
that I actually got what each step was acheiving - thanks for sharing this
with me - magic.
--
Sue Compelling


:

Try this
Try this sue:

=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIMEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))


Submitted via EggHeadCafe - Software Developer Portal of Choice
Wise for Visual Studio.NET 2003
http://www.eggheadcafe.com/tutorial...9b-a8eaf65a7f0f/wise-for-visual-studione.aspx
 
M

Mohammad Eahman

Thanks guys! I got this sorted ....

=IF(ISERR(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Daily",O3)+5,11)),"_","/",1)),"_","/",1)),"?",(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Daily",O3)+5,11)),"_","/",1)),"_","/",1)))

cheers,
Mo



Mohammad Rahman wrote:

extract date out of string
20-Dec-09

You guys are awesome.

Please help me with this, i need to get the date out of this file name

C:\Documents and Settings\Rez\Desktop\Excel forum\Queue Performance Phone Daily15_Dec_2009.csv


If you let me know how this works, i will be to do this myself in the future.

cheers,
Mo

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
New IEFT RFC indexed search page
http://www.eggheadcafe.com/tutorial...d-b88f3813a47e/new-ieft-rfc-indexed-sear.aspx
 
R

Ron Rosenfeld

Thanks guys! I got this sorted ....

=IF(ISERR(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Daily",O3)+5,11)),"_","/",1)),"_","/",1)),"?",(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Daily",O3)+5,11)),"_","/",1)),"_","/",1)))

cheers,
Mo

If your format is always that the Date is at the end of the file name, and
there is always a file suffix then:

=--SUBSTITUTE(MID(A1,FIND(".",A1)-11,11),"_"," "))

and format as dd mmm yyyy.

--ron
 

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