| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting dates from a String | Excel Worksheet Functions | |||
| find a year in a string of dates | Excel Discussion (Misc queries) | |||
| Insert String Between Dates in Chart Title | Excel Worksheet Functions | |||
| Picking every monday from a list of dates | Excel Discussion (Misc queries) | |||
| Dates in a text string? | Excel Discussion (Misc queries) | |||
Hybrid Mode
