|
Taking out string part which represents date
Hi,
I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~ |
Taking out string part which represents date
try
Sub getdatefromstring() For Each c In range("a2:a22")'Selection x = Mid(c, InStrRev(c, "-") + 1, 4) md = Format(DateSerial(2006, Left(x, 2), _ Right(x, 2)), "mm/dd/yyyy") MsgBox md Next c End Sub -- Don Guillett SalesAid Software "c8tz" wrote in message oups.com... Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~ |
Taking out string part which represents date
Hi
Probably rather longwinded, but it's late!! With string in A1 =--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2) &"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2)) FormatCellsNumberCustommmm yy -- Regards Roger Govier "c8tz" wrote in message oups.com... Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~ |
Taking out string part which represents date
Assuming all of your data follows the same format as your example, this
formula should work: =MID(A1,LEN(A1)-5,4) Another option would be: =MID(A1,FIND("-",SUBSTITUTE(A1,"-","",1))+2,4) HTH, Elkar "c8tz" wrote: Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~ |
Taking out string part which represents date
On Feb 2, 11:17 am, "Roger Govier"
wrote: Hi Probably rather longwinded, but it's late!! With string in A1 =--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2) &"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2)) FormatCellsNumberCustommmm yy -- Regards Roger Govier "c8tz" wrote in message oups.com... Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~- Hide quoted text - - Show quoted text - Hi, I figured out one way ... but it puts the year as 1902 and not 2002 - =DATE(MID(F2,10,2),MID(F2,8,2),1) it picks up 02 as 1902 which should be 2002 - can anyone expand from my formula ?? Thanks - meanwhile I'll try the other two - thanks alot! |
Taking out string part which represents date
Hi
=DATE(MID(F2,10,2),MID(F2,8,2),1) If your strings are always going to be of fixed length, then you only need to deal with the century within your formula. Taking the easy solution first, if all dates are after 2000, then =DATE(MID(F2,10,2)+100,MID(F2,8,2),1) If there can be dates up to 1999, but after 1950, then =DATE(MID(F2,10,2)+100*(MID(F2,10,2)<"50"),MID(F2, 8,2),1) -- Regards Roger Govier "c8tz" wrote in message ups.com... On Feb 2, 11:17 am, "Roger Govier" wrote: Hi Probably rather longwinded, but it's late!! With string in A1 =--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2) &"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2)) FormatCellsNumberCustommmm yy -- Regards Roger Govier "c8tz" wrote in message oups.com... Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~- Hide quoted text - - Show quoted text - Hi, I figured out one way ... but it puts the year as 1902 and not 2002 - =DATE(MID(F2,10,2),MID(F2,8,2),1) it picks up 02 as 1902 which should be 2002 - can anyone expand from my formula ?? Thanks - meanwhile I'll try the other two - thanks alot! |
Taking out string part which represents date
On 1 Feb 2007 16:59:27 -0800, "c8tz" wrote:
Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~ What do you mean by "specify that"? If you mean to extract the last four digits and interpret them in terms of month and year, then this formula should work: =--TEXT(MID(A1,LEN(A1)-4,4),"00""/01/""00") will return a serial date. Then format the cell: Format/Cells/Number/Custom Type: mmm yyyy Or, if you just want text, and not a serial date: =TEXT(--TEXT(MID(A1,LEN(A1)-4,4),"00""/01/""00"),"mmm yyyy") If you mean something else, please be more specific. Best, --ron |
Taking out string part which represents date
This should do it but if your text is not always the same use my last post
macro =DATE("20"&MID(E2,10,2),MID(E2,8,2),1) -- Don Guillett SalesAid Software "c8tz" wrote in message ups.com... On Feb 2, 11:17 am, "Roger Govier" wrote: Hi Probably rather longwinded, but it's late!! With string in A1 =--(1&"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+1,2) &"/"&MID(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))+3,2)) FormatCellsNumberCustommmm yy -- Regards Roger Govier "c8tz" wrote in message oups.com... Hi, I have a string eg.125-64-0502L where 0502 represents May 2002 - how can i specify that without doing so manually. thanks in advance for your assistance, c8tz~- Hide quoted text - - Show quoted text - Hi, I figured out one way ... but it puts the year as 1902 and not 2002 - =DATE(MID(F2,10,2),MID(F2,8,2),1) it picks up 02 as 1902 which should be 2002 - can anyone expand from my formula ?? Thanks - meanwhile I'll try the other two - thanks alot! |
| All times are GMT +1. The time now is 04:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com