Dates in Excel.

[Mike B]

Hi All
Is there a way to turn '21st April 1813' into '21 Apr 1813' in Excel.
Usually in a register I get a date like 21st April in one column and 1813 in the next.

OK I can concatenate (whatever) and join them together but still have to abbreviate the month and get get rid of the st in the day.

I could use find and replace but with a load of dates that would take ages.

Any Ideas |help|

[Neil Wilson]

Mike
Depends where the dates are, if all in one column, you can highlight the column and rightclick and select format cells. If the style you want isn't shown in the dates section, I believe you can select custom and make your on style.
Good luck

[Mike B]

Hi Neil
I've tried that one. No luck!

[suemalings]

If you format your cells as text it will work, but you have to type in the date as you want it.

[Mike B]

I've hundreds of them.

[Geoffers]

I could use find and replace but with a load of dates that would take ages.
Any Ideas

It shouldn't take long. as mentioned, highlight the column and use the find and replace to replace the 'th' and 'st' then to amend the month to your abbreviation - fourteen edits in all which should take all of five minutes, just enough time to make a cuppa and let it brew. It's an awful lot quicker than typing them out everytime.

[Mike B]

Hi Geoffers
That was the only way I knew I could do it using many Replace commands or writing a mcaro that would do it all at the press of a button.

I was hoping I could use a function like left right or mid that would instantly edit each entry.