Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg 30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg 9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg Fh 18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character 218, the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true, resulting
in an eternal loop. What is the problem? And does anyone have a nice solution?

Thanks
Stefan

Cell.Characters Delete/Insert fails with 256+ characters.

But what are you trying to do, in your example what text do you want to end
up with.

Regards,
Peter T


"svai" wrote in message
...
Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed
until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg 30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg 9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg Fh
18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character 218,
the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true,
resulting
in an eternal loop. What is the problem? And does anyone have a nice
solution?

Thanks
Stefan

Ok, that explains why... Thanks.

In my example, if I extract the part where it fails, I want to make
"Gyllenhjelmsg Fh 18" (with two space characters between "Gyllenhjelmsg" and
"Fh") into "Gyllenhjelmsg Fh 18" (with just one space character), just to
shorten the length of the text. In this example the difference is small, but
sometimes I have "Gyllenhjelmsg Fh 18" and want to
keep just one space character.

Best regards
Stefan

"Peter T" wrote:

Cell.Characters Delete/Insert fails with 256+ characters.

But what are you trying to do, in your example what text do you want to end
up with.

Regards,
Peter T


"svai" wrote in message
...
Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed
until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg 30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg 9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg Fh
18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character 218,
the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true,
resulting
in an eternal loop. What is the problem? And does anyone have a nice
solution?

Thanks
Stefan

If I follow, any two or more spaces should only be a single space. Following
will not be affected by that 256 limit, and very considerably faster

Dim pos As Long
Dim sText As String
Dim c As Range

Set c = ActiveCell ' or in a loop

If InStr(1, c.Value, " ") Then
sText = cValue
Do
pos = InStr(sText, " ")
If pos Then sText = Replace(sText, " ", " ") ' not xl97
Loop Until pos = 0
c.Value = sText
End If

End Sub

Might be worth checking first (in the loop) that c refers to a non-formula
text cell

Regards,
Peter T


"svai" wrote in message
...
Ok, that explains why... Thanks.

In my example, if I extract the part where it fails, I want to make
"Gyllenhjelmsg Fh 18" (with two space characters between "Gyllenhjelmsg"
and
"Fh") into "Gyllenhjelmsg Fh 18" (with just one space character), just to
shorten the length of the text. In this example the difference is small,
but
sometimes I have "Gyllenhjelmsg Fh 18" and want to
keep just one space character.

Best regards
Stefan

"Peter T" wrote:

Cell.Characters Delete/Insert fails with 256+ characters.

But what are you trying to do, in your example what text do you want to
end
up with.

Regards,
Peter T


"svai" wrote in message
...
Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed
until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg
30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg
9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg
Fh
18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character
218,
the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true,
resulting
in an eternal loop. What is the problem? And does anyone have a nice
solution?

Thanks
Stefan

c.value = application.trim(c.value)

will remove leading/trailing/and those extra internal spaces.

svai wrote:

Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg 30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg 9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg Fh 18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character 218, the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true, resulting
in an eternal loop. What is the problem? And does anyone have a nice solution?

Thanks
Stefan

--

Dave Peterson

Nice! Works like a charm, thanks!

"Peter T" wrote:

If I follow, any two or more spaces should only be a single space. Following
will not be affected by that 256 limit, and very considerably faster

Dim pos As Long
Dim sText As String
Dim c As Range

Set c = ActiveCell ' or in a loop

If InStr(1, c.Value, " ") Then
sText = cValue
Do
pos = InStr(sText, " ")
If pos Then sText = Replace(sText, " ", " ") ' not xl97
Loop Until pos = 0
c.Value = sText
End If

End Sub

Might be worth checking first (in the loop) that c refers to a non-formula
text cell

Regards,
Peter T


"svai" wrote in message
...
Ok, that explains why... Thanks.

In my example, if I extract the part where it fails, I want to make
"Gyllenhjelmsg Fh 18" (with two space characters between "Gyllenhjelmsg"
and
"Fh") into "Gyllenhjelmsg Fh 18" (with just one space character), just to
shorten the length of the text. In this example the difference is small,
but
sometimes I have "Gyllenhjelmsg Fh 18" and want to
keep just one space character.

Best regards
Stefan

"Peter T" wrote:

Cell.Characters Delete/Insert fails with 256+ characters.

But what are you trying to do, in your example what text do you want to
end
up with.

Regards,
Peter T


"svai" wrote in message
...
Hi!

I have created a loop in vb which deletes unnecessary spaces like this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed
until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg
30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg
9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg
Fh
18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character
218,
the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like it
doesn't work since the while-statement is then evaluated as true,
resulting
in an eternal loop. What is the problem? And does anyone have a nice
solution?

Thanks
Stefan

Go with Dave's Trim, not sure why I didn't suggest that myself!

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
If I follow, any two or more spaces should only be a single space.
Following will not be affected by that 256 limit, and very considerably
faster

Dim pos As Long
Dim sText As String
Dim c As Range

Set c = ActiveCell ' or in a loop

If InStr(1, c.Value, " ") Then
sText = cValue
Do
pos = InStr(sText, " ")
If pos Then sText = Replace(sText, " ", " ") ' not xl97
Loop Until pos = 0
c.Value = sText
End If

End Sub

Might be worth checking first (in the loop) that c refers to a non-formula
text cell

Regards,
Peter T


"svai" wrote in message
...
Ok, that explains why... Thanks.

In my example, if I extract the part where it fails, I want to make
"Gyllenhjelmsg Fh 18" (with two space characters between "Gyllenhjelmsg"
and
"Fh") into "Gyllenhjelmsg Fh 18" (with just one space character), just to
shorten the length of the text. In this example the difference is small,
but
sometimes I have "Gyllenhjelmsg Fh 18" and want to
keep just one space character.

Best regards
Stefan

"Peter T" wrote:

Cell.Characters Delete/Insert fails with 256+ characters.

But what are you trying to do, in your example what text do you want to
end
up with.

Regards,
Peter T


"svai" wrote in message
...
Hi!

I have created a loop in vb which deletes unnecessary spaces like
this:

For i = 1 To langd - 1

If c.Characters(i, 1).Text = " " Then
While c.Characters(i + 1, 1).Text = " "
c.Characters(i + 1, 1).Delete
Wend
End If

Next

This is nested inside a "For each c in range..." and has never failed
until
applied on on a cell containing this text:

Trädgårdsg 31, Trädgårdsg 26, Trädgårdsg 23, Trädgårdsg 21, Trådgårdsg
30,
Tingstug 16, Sörgärdsg 22, Sörgärdsg 20, Rådmansg 1, Nyg 23, Nikanderg
9,
Nikanderg 6, Nikanderg 11, Järnvägsg 4, Hospitalsg 15, Gyllenhjelmsg
Fh
18,
Brinkska V 2 FH, Brinkska V 2 A

c.characters.count returns 257 and the loop gets stuck on character
218,
the
space after Gyllenhjelmsg where there are two spaces. The line
"c.Characters(i + 1, 1).Delete" is then excecuted, but it seems like
it
doesn't work since the while-statement is then evaluated as true,
resulting
in an eternal loop. What is the problem? And does anyone have a nice
solution?

Thanks
Stefan