DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

[SSJ]

Hello!

When I download data into excel some of the dates in the data comes as:

2Sep'06


I edit the cell by taking out the apostrophe and the value becomes a date. What formula / function can I use to take out or clean this apostrophe and make it a date?

Thanks
SJ

 

[Sandy]

Try this,

open vbe(alt + F11) paste code into a new module(insert ==> module).
Then select the dates you want fixed and run code.

Sub Test()
Dim mcell As Range
For Each mcell In Selection
mcell.Value = Replace(mcell, "'", "")
Next
End Sub


Sandy

 

[Ron Coderre]

Try this:

Select your range of "dates"

From the Excel main menu:
<edit><replace>
Find what: ~'
Replace with: (leave this blank)
Click the [Replace All] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[SSJ]

Ron,

Beauuuuuuuuuuuuuuutiful! This is exactly what I was looking for.

Thanks
SJ

Try this:

Select your range of "dates"

From the Excel main menu:
<edit><replace>
Find what: ~'
Replace with: (leave this blank)
Click the [Replace All] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Hello!

When I download data into excel some of the dates in the data comes as:

2Sep'06


I edit the cell by taking out the apostrophe and the value becomes a
date. What formula / function can I use to take out or clean this
apostrophe and make it a date?

Thanks
SJ