DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SSJ - 27 Oct 2006 16:50 GMT

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

Reply to this Message

Sandy - 27 Oct 2006 17:05 GMT

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

> Hello!
>
[quoted text clipped - 44 lines]
>
> ------=_NextPart_000_0009_01C6F9BE.10874200--

Reply to this Message

Ron Coderre - 27 Oct 2006 17:28 GMT

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!
>
[quoted text clipped - 6 lines]
> Thanks
> SJ

Reply to this Message

SSJ - 27 Oct 2006 20:34 GMT

Ron,

Beauuuuuuuuuuuuuuutiful! This is exactly what I was looking for.

Thanks
SJ

> Try this:
>
[quoted text clipped - 25 lines]
>> Thanks
>> SJ

Reply to this Message