I think the easiest way would be to select the column and do a global
replace of "." for "/".
You could record a macro as you do it ... should look something like this:
Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
A slightly modified version:
Columns("B:B").Replace _
What:=".", _
Replacement:="/", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
In my testing the text was turned into dates with a date format "dd/mm/yyyy"
(which is what I expected).
Be careful if you opt for a VBA solution as dates can become "muddled" by
the USA-centricity of VBA
Regards
Trevor
"concatenator" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I often have to deal with a column of text in the format of xx.xx.19xx.
> However this is not a recognised date format.
>
> Is there a macro or command that will allow me to select all of these
> cells and convert them into a recognised date format, eg xx/xx/19xx or
> xx-jan-19xx?
>
> At present i can split them out to columns and then concatenate the
> data into a date format but this only works the cell in Row 1,not for
> the whole selection. Any suggestions? Cheers
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>
Similar Threads
