editing imported data

[MB]

I have imported a large amount of data into excel from a
proprietary software platform, unfortunatly I have only
be able to do so in a way that leaves a leading
apostrophe (') on each cell ex: '45 '34.5

Is there some way i could simply remove the apostrophe
from each cell?, as it causes excel to interpret the data
as text and therefore prevents any calculation

MB

 

[Arvi Laanemets]

Hi

From menu Edit.Replace
Into 'Find' field enter apostrophe, into 'Replace with' field nothing, and
press 'Replace All'

 

[Robert Christie]

Hi MB
Go to David McRitchie web site and download his Trimall
macro program.
Select your text and run the macro.
Arvi Laanemets replace has never worked for me.
HTH

Bob C

 

[Robert Christie]

Sorry, David McRichie web Site
http://www.mvps.org/dmcritchie/excel/excel.htm

Bob C

 

[Arvi Laanemets]

Hi

Hi MB
Go to David McRitchie web site and download his Trimall
macro program.
Select your text and run the macro.
Arvi Laanemets replace has never worked for me.

OK, it looks like apostrophe is a special case here - I tested it and it
doesn't work for me too.

But this method worked (I have used it to convert text-formatted numbers
before, but likely it works both with numbers and texts started with
apostrophe too):

Enter a number 1 into some cell (or use some cell with 1 in it) and copy it.
Select the range with imported data, and then PasteSpecial.Multiply - all
numeric strings are converted to numbers, and apostrophes before strings are
removed .

 

[MB]

hanks all I tried the replace function and no luck, I
will try the others

MB