Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text

Raymond


Dave Peterson wrote:
Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

Raymond,
Not sure if accessing the Characters instead will help

Worksheets(1).Range("A1").Characters(2, 2).Text = "*"
You'd obviously need to get the position of the char(s) to be replaced.

NickHK

wrote in message
ups.com...
Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text

Raymond


Dave Peterson wrote:
Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is
how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

Nick,

Position of the characters is variable as they do not always have a
double comma and the characters before and after the commas can be up
to 8 chars long

What my code does is

It takes the cell, in this case R541,R542,R543,R587,R588,,R689,R780,

Then puts them in rows (because of the double comma, a blank row is
inserted between R588 and R689

R541
R542
R543
R587
R588

R689
R780

Strange thing is, my code works fine for doing the above, it removes
the very last comma in the cell, counts the number of commas in the
cell which in turn inserts the number of rows required including the
cell which has 4,087 characters (this cell produces 1,645 rows (due to
the double commas) when it shopuld only insert 1,595

It is a simple bit of coding to temove the blank rows created because
of the double commas but would rather have the ability to replace the 2
commas with only 1 comma

Raymond

NickHK wrote:
Raymond,
Not sure if accessing the Characters instead will help

Worksheets(1).Range("A1").Characters(2, 2).Text = "*"
You'd obviously need to get the position of the char(s) to be replaced.

NickHK

wrote in message
ups.com...
Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text

Raymond


Dave Peterson wrote:
Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is
how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

I put this in a few cells:

=rept("asdf,,",5000)
and converted to text.

My strings were 30,000 characters long

I ran the macro and didn't get the error message.



wrote:

Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text

Raymond

Dave Peterson wrote:
Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

--

Dave Peterson

Thanks Dave,

I found this little snippet that did the trick, think it was Tom Ogilve

Dim sStr As String, cell As Range

Count = Range("A65000").End(xlUp).Row

Range("A2:A" & Count).Select

For Each cell In Selection
sStr = cell
sStr = Application.Substitute(sStr, ",,", ",")
cell.Value = sStr
Next

Raymond

Dave Peterson wrote:
I put this in a few cells:

=rept("asdf,,",5000)
and converted to text.

My strings were 30,000 characters long

I ran the macro and didn't get the error message.



wrote:

Thanks for the code Dave but I still get the "Formula Too Long" error
when it starts hitting the rows with the long string and they are all
text

Raymond

Dave Peterson wrote:
Modified 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!

wrote:

Hi,

Using Excel 2000

Example

In a cell I have the following: R541,R542,R543,R587,R588,,R689,R780,
.............................

After R588 there are 2 commas where there should only be 1, this is how
the data is extracted from our quotes system and nothing can be done
about it

Trying to replace the 2 commas with only 1 comma returns a formula too
long error when there is a large amount of data in the cell (4542
characters is when the error first appeared)

Anybody know of any function which will do the required task without
receiving the error

Thanks in advance

Raymond Allan

--

Dave Peterson

--

Dave Peterson