get rid of single quote in field from Access export

[Judy Ward]

I am exporting from Access to Excel and ending up with a single quote in
front of the dates--which makes them a text field. This matters because I am
using the dates as a row source for a chart. If Excel knows they are Friday
dates, it fills in the missing Friday dates for me (which is what I want).

I have tried selecting the cells and formatting them as date fields and that
doesn't work. Manually removing the single quote in front of the date fields
has been the only thing that has worked, but I need to be able to do this in
code.

Does anyone have a solution for me?
Thank you,
Judy

 

[FSt1]

hi
in a blank cell off to the side, enter the number one(1). copy it. high
light the column of text dates. then paste special >muliply. you may loose
your formating as it changes to number but can easily reformat to date.

regards
FSt1

 

[Rick Rothstein]

Believe it or not, it is as simple as just assigning the Value back to
itself. For example...

Single Cell: Range("A2").Value = Range("A2").Value
Rangel Of Cells: Range("A2:F9").Value = Range("A2:F9").Value
Entire Column: Columns("A").Value = Columns("A").Value

 

[Per Jessen]

Hi Judy

Enter 1 in an unused cell, and copy the cell. Select "Date cells" and goto
Edit > Paste Special > Operation: Multiply > OK. Format the "Date" cells as
date.

Hopes this helps

 

[Gord Dibben]

Usually running the data though Data>Text to Columns>Next>Next>Column Data
Format>Date(choose format from dropdown)>Finish will do the job.


Gord Dibben MS Excel MVP