importing and converting text from Access

[John Finagin]

I do a lot of work with student examination data which is
collected in Access and exported to Excel. In Excel the
data - in the form of grades - is converted to numerical
values so analysis and prediction can occur.

The problem is that imported text is prefixed by an
apostrophe (e.g. 'A for an A grade) that I simply can't
remove. If the text is then replaced by number values the
numbers retain the apostrophe and text characteristics, so
a long time is then spent manually cleaning the data
before I can work with it. Despite working with a lot of
IT teachers and technicians no one knows the answer & I'm
sure there's got to be a solution.

Can't find it in the Excel manual. Is there anyone out
there who can help?

 

[GerryK]

I have to clean up things from my data from time to time
but, honestly do not know what Access has done to place
the ' in front of text.
Try these and if they do not work for you perhaps another
solution may surface.

=SUBSTITUTE(A1,"'","")+0
or
=--MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT
("1:100")),1)),0),100-SUM(--ISERROR(1*MID(A1,ROW(INDIRECT
("1:100")),1)))) ...array entered (Ctrl,Shift,Enter)

HTH

 

[Mark Graesser]

Gerry
A simple =A1 will also remove the '. You could then Copy and Paste_Special>Values over the formula to lock it in. You can't Paste_Special over the original data, for some reason it will retain the ' if you do that

The Substitute function won't work for 2 reasons. The ' isn't really a character in the cell so it won't be replaced, and the values are text so using the + will return a Value error

I was thinking that this might be done during the process used to convert the letters to numbers. Just need to know what technique the OP is using

Regards
Mark Graesse
(e-mail address removed)

----- GerryK wrote: ----

I have to clean up things from my data from time to time
but, honestly do not know what Access has done to place
the ' in front of text
Try these and if they do not work for you perhaps another
solution may surface

=SUBSTITUTE(A1,"'","")+
o
=--MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIREC
("1:100")),1)),0),100-SUM(--ISERROR(1*MID(A1,ROW(INDIREC
("1:100")),1)))) ...array entered (Ctrl,Shift,Enter

HT