| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
|
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-- |
|
#3
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenating a Text and a Date without losing orginal Date Format | Excel Discussion (Misc queries) | |||
| Mileage Claim Formula | New Users to Excel | |||
| Change the size of text used in a Form combo box | Excel Discussion (Misc queries) | |||
| how can I make a form number change everytime the form is opened | Excel Worksheet Functions | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Linear Mode
