delete commas in spreadsheet but not in formulas

C

cynwalker

I have a giant workbook someone gave me and I need to take all of the commas
out of the text but not out of the formulas. Does anyone know how to do this?
 
D

Dave Peterson

Select the range to fix.
Edit|Goto|special|check Constants
(and uncheck Numbers, Logicals, Errors????)

Now your original selection has been reduced to just the cells with constants.

Edit|replace
what: , (comma)
with: (leave blank)
replace all

Be aware that if you have a string like:
12,32341,1,23,12

it'll come out as a number (not text)

And if your string is:
12,32341,1,23,12,111111111111111111,1111111111

Your string will be converted to a number with only 15 significant digits.
 
G

Gord Dibben

Right-click on first sheet tab and "Select all sheets"

CTRL + a(twice in 2003) on activesheet.

F5>Special>Constants checkmark in "Text" and OK

Edit>Replace>Options>Within>Workbook

Replace what: ,

Replace with: space or nothing

Replace all.

Right-click on the sheet tab and "Ungroup sheets"


Gord Dibben MS Excel MVP
 
Top