Remove Non-Printing Symbols

[John]

Please help. I have a spreadsheet that is generated from
a system download, manipulated in Access and outputted as
an Excel sheet. In order to get the data to export
correctly, it is formatted as TEXT. This causes the data
in Excel to have a non-printing symbol, '(apostophe), in
front of each cells data. I need to remove this so as I
can use VLOOKUP to reference data in another another
Workbook. I have tried re-formatting the data in Excel
as "General" or as "Number" but this doesn't help.

 

[Andy B]

Hi

One way. Copy a blank, unused cell. Then Edit / Paste Special . . . Add.
This should make your 'numbers' into numbers!

 

[Andy B]

Sorry. Before you Edit / Paste Special . . . Add, select the range you need
to alter!!

 

[John]

Thanks Andy, this works a treat. I don't understand why
though. The only small niggle that I now have is that I
lose the leading zero's from some of the entries. I guess
that is the price I must pay, unless you know different.

John

 

[Andy B]

If the numbers are real numbers, they shouldn't have leading zeroes!! You
could try a custom format like 00000 which will make all of the numbers 5
digits with as many leading zeroes as necessary.