VBA Change Cell Text

[satkin2]

Hi,

I've got a spreadsheet that has a row of dates in the format _31May2011 _30Jun2011 _31Jul2011 etc;
I'm going to link into these dates in a chart, so want to remove the _ so as that I can use them as dates, currently they are treated as text.

How can I change the cell value to remove the _ and set the new field to be in date format?

Many thanks

 

[Sektor]

No, its 31May2011, no spaces

So, add them!

 

[satkin2]

So, add them!

How do I do this for multiple columns? I've got a few years worth of dates, not just the ones I used as an example. Going into each cell and putting the space in would not be suitable as this spreadsheet will get overwritten each month, putting it back to the original _31May2011 etc format, so if I could VBA it to do this it would be great.

 

[CharlesChuckieCharles]

A1='31may2011

b1=DATEVALUE(A1)
or
b1=A1+0

 

[Peter_SSs]

Try this. After the Find/Replace to remove the underscore

1. Select the column by clicking its heading label.

2. Data|Text To Columns|Finish