Can not replace commas - "formula too long"

[walterbyrd]

I am trying to replace the commas in a big spreadsheet, so that I can
save the spreadsheet as a csv file. But, I keep getting the error
"formula too long." Some cells have a lot of informaion - not
formulas. Anyway, what can I do?

 

[Bernie Deitrick]

Insert a new worksheet, and in cell A1 enter

=SUBSTITUTE(Sheet1!A1,",","")

and copy to as many cells as you need to cover the entire sheet.

HTH,
Bernie
MS Excel MVP

 

[walterbyrd]

Insert a new worksheet, and in cell A1 enter

=SUBSTITUTE(Sheet1!A1,",","")

Thanks, but I am getting #REF! everywhere except cell A1.

The reference cell does change everywhere I copied the formula, i.e.

=SUBSTITUTE(Sheet1!A2,",","") in A2
=SUBSTITUTE(Sheet1!A3,",","") in A3
=SUBSTITUTE(Sheet1!A4,",","") in A4

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Try this to replace all commas with nothing.

Sub replacecomma()
Cells.Replace ",", ""
End Sub

 

[Bernie Deitrick]

Walter,

Do you have a Sheet1? Is the cell on the new sheet formatted as general? Is the formula in cell A1
returning the correct value?

I have no idea why you cell refs aren't incrementing as long as cell A1 is working...

HTH,
Bernie
MS Excel MVP

Insert a new worksheet, and in cell A1 enter

=SUBSTITUTE(Sheet1!A1,",","")

Thanks, but I am getting #REF! everywhere except cell A1.

The reference cell does change everywhere I copied the formula, i.e.

=SUBSTITUTE(Sheet1!A2,",","") in A2
=SUBSTITUTE(Sheet1!A3,",","") in A3
=SUBSTITUTE(Sheet1!A4,",","") in A4

 

[Dave Peterson]

Make sure you put that first formula in A1.

(maybe you put it row 3 and dragged up or in column E and dragged to the left?)

 

[walterbyrd]

Make sure you put that first formula in A1.

(maybe you put it row 3 and dragged up or in column E and dragged to the left?)

I had the first formula was in A1. It seems to working now, although I
am not sure why.

Thanks for your help.