Copy pasting and reformat Date

[whatzzup]

Good morning, i am currently repeating an operation in several cells as
follows: Copy 19SEP07, Paste Values, F2, Return.

This is achive a date format 19-SEP-07

The reason was i had an original date 19SEP07*, this was brought in from a
lookup formula soit came in with the asteric at the end,( * just mean the
date is constrained) so when it comes in i need to get it in proper date to
show 19-Sep-07.

So initially i used the MID function to loose the * at the end.

Is there a Macro i can write to do all this for me? So step one is to loose
the * then paste back into the cell as 19-SEP-07?

Thanks All...

 

[Dave Peterson]

I could select the range to fix
Then
Edit|Replace
what: ~*
with: (leave blank)
replace all

And all those cells got converted to dates.

If you really need a macro, you could record one when you do it manually.

========
The asterisk is a wildcard character. Excel uses the tilde to indicate that the
next character is really an asterisk--not the wildcard.

Same thing with:
~* for *
~? for ?
~~ for ~

 

[Gord Dibben]

Data>Text to Columns>Fixed Width.

Drop a line to left of asterisk and Next.

Select the asterisk column and "Do not Import" and Finish.


Gord Dibben MS Excel MVP