Editing Numerous Formulas Quickly

L

Leslie

I've downloaded a personnel list from a database into an
Excel file consisting of 360,000 cells. The only problem
is that, during the conversion, an apostrophe (') has been
added to the beginning of the FORMULA in most of the
cells. For instance, when you look at the formula bar, or
if you double click on each cell,
you will see: 'Anderson
Instead of: Anderson

I need to get rid of all of these apostrophes in the
FORMULAS of each cell without having to manually edit each
of the 360,000 cells.

Please help. Thanks.
 
P

Peo Sjoblom

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
select the import and run the macro (alt + f8 and select RemApostrophe)
 
A

Arvi Laanemets

Hi

Copy a cell with number 1 in it
Select the range with non-empty data
PasteSpecial.Multiply - all apostrophes are removed (additionally all
numeric/date strings are converted to numbers)
 
L

Leslie

Thanks!! I'll try it tomorrow!

-----Original Message-----
Hi

Copy a cell with number 1 in it
Select the range with non-empty data
PasteSpecial.Multiply - all apostrophes are removed (additionally all
numeric/date strings are converted to numbers)


--
(When sending e-mail, use address [email protected])
Arvi Laanemets





.
Click to expand...
 
Top