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 thes
cells and convert them into a recognised date format, eg xx/xx/19xx o
xx-jan-19xx?

At present i can split them out to columns and then concatenate th
data into a date format but this only works the cell in Row 1,not fo
the whole selection. Any suggestions? Cheer

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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