![]() |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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))) |
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 |
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 |
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