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
SSJ wrote:
> 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
> ------=_NextPart_000_0009_01C6F9BE.10874200
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 1526
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.2900.2963" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff>
> <DIV><FONT face=Tahoma>Hello!</FONT></DIV>
> <DIV><FONT face=Tahoma></FONT> </DIV>
> <DIV><FONT face=Tahoma>When I download data into excel some of the dates in the
> data comes as:</FONT></DIV>
> <DIV><FONT face=Tahoma></FONT> </DIV>
> <DIV>
> <TABLE style="WIDTH: 55pt; BORDER-COLLAPSE: collapse" cellSpacing=0
> cellPadding=0 width=73 border=0 x:str>
> <COLGROUP><FONT face=Tahoma>
> <COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2336"
> width=73></FONT>
> <TBODY>
> <TR style="HEIGHT: 14.25pt" height=19>
> <TD class=xl24
> style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 55pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent"
> width=73 height=19><FONT
> face=Tahoma>2Sep'06</FONT></TD></TR></TBODY></TABLE></DIV>
> <DIV><FONT face=Tahoma></FONT> </DIV>
> <DIV><FONT face=Tahoma>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?</FONT></DIV>
> <DIV><FONT face=Tahoma></FONT> </DIV>
> <DIV><FONT face=Tahoma>Thanks</FONT></DIV>
> <DIV><FONT face=Tahoma>SJ</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_0009_01C6F9BE.10874200--
|
Similar Threads
