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

 

[Trevor G]

Use Find and Replace

In the replace add the _ then click replace all

 

[Sektor]

Is _31May2011 _30Jun2011 _31Jul2011 in one cell?

 

[satkin2]

Is _31May2011 _30Jun2011 _31Jul2011 in one cell?

No, sorry I should have made myself clearer, each month is a column, ie: Col B = _31May2011, Col C = _30Jul2011.

Use Find and Replace

In the replace add the _ then click replace all

This gets rid of the _ but what is left ie: 31May2011 is in General format, and it is treated as a string, not a date field. I'd like to make it a date field too.

 

[Sektor]

So, what prevents you from setting format as Date?

 

[satkin2]

So, what prevents you from setting format as Date?

So I've done the find and replace and the field is now 31May2011, but when I click on the cell it shows '31May2011, making it a text string. Going to Format and setting as date doesn't set it to date, it remains as a General, because it's a text string.

I've subsequently tried running a find and replace, replacing the ' with nothing, but this didn't work either.

 

[Sektor]

' makes Excel treat content as text. Before removing "_", make cell's format as Date.

 

[satkin2]

' makes Excel treat content as text. Before removing "_", make cell's format as Date.

I tried that, but it still doesn't work. I did the following...

• Set format as date
• Find and replace _ with <blank>
• Checked format, it still shows as Date
• Checked cell value it still has '31May2011 so still treats as string.

 

[Sektor]

And do u have spaces like "31 May 2011"?

 

[satkin2]

And do u have spaces like "31 May 2011"?

No, its 31May2011, no spaces