"Formula is to long" error.

C

ChrisB

I am trying to do a Find and Replace on all commas in my spreadsheet. I
have a description field which is extremely long, there are about 8
columns and 6400 rows...any idea how to get around this ?
 
D

Dave Peterson

You can use code to do as many as possible, then loop through each cell that
complained.

This may give you a starting point. I saved it from a previous post:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = ",,"
AfterStr = ","

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!
 
B

Bill Ridgeway

I don't understand the question.

If a formula is too long (either by the number of arguments or the number of
characters) you need to split it over two cells and then have a formula in a
third column to interpret the output of the first two. A comma in a formula
is there as a separator and as such is a vital part of the formula and
cannot be taken out.

Regards.

Bill Ridgeway
Computer Solutions
 
C

ChrisB

hrmm...thanks for this!!!

Just wondering - where do I set what I want it to find and what to
replace with? Ive found a few places, and also, would it work if I want
to replace it with nothing?

thanks for all your help!!
 
B

Bill Ridgeway

If the comma is part of the structure of a formula it is needed and
shouldn't be changed or removed. If, however, the comma is the printed
output (result) of a formula it may be changed from a comma to a blank by
substituting (",") with ("") ignore brackets. That will reduce the length
of the formula by the number of commas contained therein.

Regards.

Bill Ridgeway
Computer Solutions
 
C

ChrisB

Thanks Bill.

to answer your questions, It doesnt matter if its 4 columns and 4 rows.
The problem is I am referencing another sheet using the VLOOKUP
function, and the description columns have ALOT of characters, probably
about 1500 each or so. And the find/replace wont work around that,
hence the "formula to long" error. My main concern why I need the
commas gone is that I am using a tab delimited file to upload a
multitude of products at once to a MySQL database on my web host, and
Im assuming the php script in which I use to upload is interpreting the
seperators wrong, and thats why Im getting these errors with any
commas.


thanks for your help guys!

I shall try this and post my results.
 
D

Dave Peterson

The original poster wanted to change ",," to a "," (double comma to single
comma).

You'd change these two variables to what you need.

BeforeStr = ",,"
AfterStr = ","

The BeforeStr is the before string. The AfterStr is the after string. <vbg>
 
Top