modify contents structure of multiple cells

flox

New Member
Joined
Jun 6, 2012
Messages
3
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,435
Messages
6,101,853
Members
447,758
Latest member
jojo3strada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top