Taking out string part which represents date

C

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~
 
D

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
 
R

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
 
E

Elkar

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
 
D

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
 
C

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 -
Click to expand...

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

Roger Govier

Hi
=DATE(MID(F2,10,2),MID(F2,8,2),1)
Click to expand...
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)
 
R

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~
Click to expand...

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
 
D

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