Extracting date from strings

[chiswickbridge]

My Excel work has dates as stings in dd.mm.yy format. I need to convert the dd.mm.yy to date format Example : A1 contains 28.05.15 which is todays date but has been input as string. I want an easy formula to show the date in B1 as 28May2015. Help

 

[bbott]

Maybe:

=TEXT(DATE(20&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2)),"ddmmmmyyyy")

 

[Rick Rothstein]

You can use this formula...

=0+SUBSTITUTE(A1,".","/")

It will return the date as an offset serial value (how Excel stores dates), so you will have to format the cell with the date format of your choosing, then you can copy it down as needed.

 

[gaz_chops]

Or=substitute(A1,".","/")Format cells as date

 

[chiswickbridge]

Tks bbot & Rick....it worked perfectly.............gaz_chops...tks, but the value is still as string unable to format it