Strip apostrophe from cells

  • Thread starter Thread starter jfrizelle
  • Start date Start date
J

jfrizelle

Hi. I am exporting data to Excel from an Access database. There is a
large volume of data, and much of the text is being preceded by the
apostrophe ('). This does not appear in the cell - only in the formula
bar.

Is there any function I can use to get rid of it? I've tried Clean,
Left, Right, etc, but they don't seem to see it. Going into each cell
is obviously not an option, and I'm stuck - please help!!

Thanks a lot,
Jennie.
 
this is because, preceding a number value with an
apostrophe lets excel know it is text. if you imported
numbers beginning with a zero, the zero would be dropped
off if it was not preceded by an apostrophe. try using a
blank column and put this formula in the row of the first
record. =SUBSTITUTE(A1,"'","") copy down. then if you
want to replace the contents of the column with the
apostrophe copy the new column, and select that column
again. choose edit>paste special...>and choose values.
now you can replace the first column with the new data
which should be clean of apostrophes.
 
You can try this:

Right click in an*empty* cell and choose "Copy".
Then select all the cells you wish to change.
Right click in this selection and choose "PasteSpecial".
Then click "Add", then <OK>.

Your text will become text without the apostrophe, and your numerical text
will become true numbers, with the ability to be used in calculations.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi. I am exporting data to Excel from an Access database. There is a
large volume of data, and much of the text is being preceded by the
apostrophe ('). This does not appear in the cell - only in the formula
bar.

Is there any function I can use to get rid of it? I've tried Clean,
Left, Right, etc, but they don't seem to see it. Going into each cell
is obviously not an option, and I'm stuck - please help!!

Thanks a lot,
Jennie.
 
Back
Top