How do I delete hidden character in Excel?

I

Isa

Hi,
I have exported a table from Access 2000 to Excel 2000.
However, all the fields have a hidden character in front which is '
I am unable to do any formulas as it doesn't recognise it as a number but
has the number format.
I can't get rid of it when doing a search and replace either.
Can you help?

Thanks.
 
N

Nick H

Isa,

You could try using the CLEAN worksheet function. e.g. if your data is
on Sheet1 create a new sheet and in Cell A1 of that sheet enter
=CLEAN('Sheet1'!A1) and drag-copy that formula to an area the same size
as the data on Sheet1.

Having done that you may want to copy the data on your new sheet and
pastespecial the values to get rid of the formulas.

HTH

Nick.
 
P

Peo Sjoblom

Try a macro

Sub RemApostrophe()
Dim Rng As Range
Dim myCell As Range
Set Rng = Selection
For Each myCell In Rng.Cells
myCell.Value = myCell.Value
Next myCell
End Sub

press Alt + F11, click insert>module and paste in the above, press Alt + Q

select the range and press Alt + F8 and double click the macro name

I don't think CLEAN will fix an apostrophe
Another way might be to select the column, do data>text to column and press
finish
 
M

Michael

Hi Isa. You might also use the Value function. This will transform the text
you exported from Access to a number. Assume your data is in A1 to A100, in
B1 type =Value(A1) and copy down to B100. Then copy B1:B100 and Paste
Special - Values into A1:A100. HTH
 
Top