Taking out string part which represents date

[c8tz]

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~

 

[Don Guillett]

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

 

[Roger Govier]

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))

Format>Cells>Number>Custom>mmm yy

 

[Guest]

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

 

[Don Guillett]

OR to get May 2002

Sub getdatefromstring()
For Each c In Selection
x = Mid(c, InStrRev(c, "-") + 1, 4)
md = Format(DateSerial(Right(x, 2), Left(x, 2), 1), "mmm yyyy")
MsgBox md
Next c
End Sub

 

[c8tz]

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))

Format>Cells>Number>Custom>mmm yy
--
Regards

Roger Govier









- 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!

 

[Roger Govier]

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)

 

[Ron Rosenfeld]

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

 

[Don Guillett]

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)