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
news:%23jIPWo6%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:4F845FAE-17D4-47DD-B4B1-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:F7A98BDF-AA21-4235-9EA6-(E-Mail Removed)...
>>> > 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
>>>
>>>
>>>
>
>
Similar Threads
