ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Picking up dates from within a string (https://www.excelbanter.com/excel-worksheet-functions/247591-picking-up-dates-within-string.html)

Sue Compelling

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

T. Valko

Picking up dates from within a string
 
Try this...

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

Format as Date

--
Biff
Microsoft Excel MVP


"Sue Compelling" wrote in message
...
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




Jacob Skaria

Picking up dates from within a 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


"Sue Compelling" wrote:

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


Jacob Skaria

Picking up dates from within a string
 
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


"Sue Compelling" wrote:

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


Sue Compelling

Picking up dates from within a string
 
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
--
Sue Compelling


"Jacob Skaria" wrote:

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


"Sue Compelling" wrote:

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


Jacob Skaria

Picking up dates from within a string
 
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


"Sue Compelling" wrote:

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
--
Sue Compelling


"Jacob Skaria" wrote:

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


"Sue Compelling" wrote:

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


Sue Compelling

Picking up dates from within a string
 
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.
--
Sue Compelling


"Jacob Skaria" wrote:

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


"Sue Compelling" wrote:

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
--
Sue Compelling


"Jacob Skaria" wrote:

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


"Sue Compelling" wrote:

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


John_John

Picking up dates from within a string
 
Try this sue:

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

Mohammad Rahman

extract date out of string
 
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)))+TIM EVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))

Previous Posts In This Thread:

On Wednesday, November 04, 2009 11:38 PM
Sue Compelling wrote:

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

On Wednesday, November 04, 2009 11:47 PM
T. Valko wrote:

Try this...
Try this...

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

Format as Date

--
Biff
Microsoft Excel MVP

On Wednesday, November 04, 2009 11:54 PM
Jacob Skaria wrote:

'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


"Sue Compelling" wrote:

On Thursday, November 05, 2009 12:11 AM
Jacob Skaria wrote:

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


"Sue Compelling" wrote:

On Thursday, November 05, 2009 1:25 AM
Sue Compelling wrote:

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


"Jacob Skaria" wrote:

On Thursday, November 05, 2009 3:01 AM
Jacob Skaria wrote:

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


"Sue Compelling" wrote:

On Thursday, November 05, 2009 5:06 AM
Sue Compelling wrote:

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


"Jacob Skaria" wrote:

On Thursday, November 05, 2009 9:34 AM
John_John wrote:

Try this
Try this sue:

=DATEVALUE(TRIM(MID(A1,FIND("DUE",A1)+3,255)))+TIM EVALUE(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/tutorials...-studione.aspx

Mohammad Eahman

extract date out of string
 
Thanks guys! I got this sorted ....

=IF(ISERR(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Dai ly",O3)+5,11)),"_","/",1)),"_","/",1)),"?",(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Da ily",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/tutorials...exed-sear.aspx

Ron Rosenfeld

extract date out of string
 
On Sun, 20 Dec 2009 02:07:57 -0800, Mohammad Eahman wrote:

Thanks guys! I got this sorted ....

=IF(ISERR(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Da ily",O3)+5,11)),"_","/",1)),"_","/",1)),"?",(SUBSTITUTE((SUBSTITUTE((MID(O3,FIND("Da ily",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


All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com