excel macro to put tick in all cells in column

[JERD1074]

I have a column in excel that has dates, i need to do 3 things to each cell in the column.

1) convert to txt by inserting a ' in front
2) if there are not 2 characters for the day and the month, add a 0 in front of each.
3) remove the /'s

there needs to be 8 characters in each cell.

ie.
5/6/2006 needs to change to 05062006

i could do it all manually, but the column has over 1000 entries, and i thought i'd come here to see if anyone could help me out.

Thanks

 

[AnotherHiggins]

I'm not sure why you would want to store your dates as text, but here's an easier way to do it:

In the next column, just use the formula:
[tab][COLOR=blue white]=text(A1,"mm/dd/yyyy")[/color]
(replace A1 with whatever cell your dates start in)

Drag the formula down the column.

The result will be text. Copy 'n' Paste Special > Values to replace the formulae with text strings.

Why do you want your dates as text, anyway? Chances are VERY good that you can do whatever you need while keeping the dates stored as numbers.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I just saw that you don't want the slashes in the date. To achieve that, the formula should be [COLOR=blue white]=text(A1,"mmddyyyy")[/color]

But, again, I strongly suggest that you leave your dates as numbers. Please explain why you feel the need to change them to text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Why not keep them as dates and format as custom..

mmddyyyy

??? Anything else seems like nonsense. Or is there some other reason?

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[KenWright]

I'm guessing it's for an export to some other app possibly?

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------