editing imported data

  • Thread starter Thread starter MB
  • Start date Start date
M

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
 
Hi

From menu Edit.Replace
Into 'Find' field enter apostrophe, into 'Replace with' field nothing, and
press 'Replace All'
 
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
 
Hi


Robert Christie said:
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.
Click to expand...


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