modify contents structure of multiple cells

[flox]

Good morning,
I am facing an issue around the structure of Excel cells. One of my employee sent me a table with 2 colums of 534 lines. Each of them contents a number such as "10042012". The person did not use the date format but the general format. I need to use these informations to filter them. How can i change all the cells at once to make them look like "10-04-2102" without having to change the 1068 cells manually? Is there a macro I can use?
Thank you very much for your help.
Flox

 

[jkpieterse]

Use this formula in an extra column:

=DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2))

 

[flox]

Hi Jan,
Thank you for the quick reply.
I have inserted a column next to one of the concerned ones I had, but how do I make the formula you gave me related (or connceted) to the one I have?
I have copied your formula as you gave it to me in the first cell of the column I have inserted but it tells me then #value!.
Sorry, I am not very good at that...

Flox

 

[flox]

Hi Jan again,

I found the way to connect them. It works.
Thank you so much.... you saved me hours of boring editing job.
Thanks again for the prompt and very efficient reply.

Flox

 

[jkpieterse]

You're welcome!
After you have the formulas, you can copy them and then paste-special, Values them on top of the original range. The formulas can then be discarded.

 

[pgc01]

Hi Flox
Welcome to the board

If this is something you'll have to do regularly, another option is to use Text to Columns. This way you can process an entire column in one sweep and the modification of the value is done in situ.

In the third panel of Text to Columns choose Date and the format you want.