multiple spaces in a string 1

[neemi]

Hi,

If I have a string where there are multiple spaces in it... for example

strS = "TEST TEST STRING TEST"

just as a random string, with random amount of spaces in the string I thought I would be able to use the replace function, to make the multiple spaces into one space. but can't seem ti get this to work.

I was thinking maybe I need to loop the string to replace multiple spaces by one space.

Any advice and help apreciated.

Cheers,
Neemi

 

[PHV]

You may try this:

While InStr(strS, "  "): strS = Replace(strS, "  ", " "): WEnd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neemi]

Thats great. solved my issue. Cheers, PH

 

[AnotherHiggins]

Another solution, without looping:

strS = [b]Application.Trim[/b](strS)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[PHV]

John, how did you know that the OP wanted an excel solution ?

 

[AnotherHiggins]

psychic

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fumei]

You Excel guys....sigh, the whole world revolves around you, doesn't it?

Just kidding.

faq219-2884

Gerry
My paintings and sculpture

 

[strongm]

Er ... I don't think that'll work ...

 

[AnotherHiggins]

You don't think what will work, strongm?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[neemi]

strS = Application.Trim(strS)

The above doesn't actually work... I have always thought that the trim removes spaces from the left and right of a string but not from wothin the string. But I tried it and application.trim produces the error method or data object doesn't exist.

 

[AnotherHiggins]

As PHV alluded to, it is a solution specific to Excel VBA.

But in Excel, Application.Trim will indeed "trim" down repeated internal spaces to single spaces as well as "trimming" off spaces from the left and right sides.

If you're using Excel, then in VBA this:

Sub TrimTest()
strS = "TEST    TEST       STRING TEST"
strS = Application.Trim(strS)
End Sub

would produce:

[tab]strS = TEST TEST STRING TEST

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[strongm]

Oops - my post was delayed by a darned meeting ... and others have already made the Excel point I was getting around to ...

 

[neemi]

sorry yes it does work.... I don't know why it didn't work just when i tried it... but now it does work after I tried it in the immediate window.

was proberbly an Id-10-t error on my behalf!


we learn something every day hey.