character.delete doesn't work

[svai]

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

 

[Peter T]

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]

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]

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

 

[Dave Peterson]

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

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

 

[svai]

Nice! Works like a charm, thanks!

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

 

[Peter T]

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

Regards,
Peter T