importing and converting text from Access

J

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?
 
G

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
 
M

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
[email protected]

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