ExcelBanter - DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM (https://www.excelbanter.com/new-users-excel/116430-date-text-form-need-change-date-form.html)

DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

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

DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

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=TahomaHello!</FONT</DIV
<DIV<FONT face=Tahoma</FONT </DIV
<DIV<FONT face=TahomaWhen 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=Tahoma2Sep'06</FONT</TD</TR</TBODY</TABLE</DIV
<DIV<FONT face=Tahoma</FONT </DIV
<DIV<FONT face=TahomaI 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=TahomaThanks</FONT</DIV
<DIV<FONT face=TahomaSJ</FONT</DIV</BODY</HTML

------=_NextPart_000_0009_01C6F9BE.10874200--

DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

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" 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

DATE IN TEXT FORM - NEED TO CHANGE IT DATE FORM

Ron,

Beauuuuuuuuuuuuuuutiful! This is exactly what I was looking for.

Thanks
SJ

"Ron Coderre" wrote in message
...
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" 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