Removing Space

[Guest]

Hello,

I have a text string look like "xxx xxxx", 2 spaces between those x's. I
would like to remove those spaces between the two strings. The left , right
function cannot be used as i have different combination of word between the
two words in the cell.

Please advice.

Thanks much.

 

[Norman Jones]

Hi Kaci,

Try:

'=============>>
Public Sub Tester1()
Dim sStr As String

sStr = "abc xyz123" '<<==== CHANGE
sStr = Replace(sStr, " ", "", 1)

End Sub
'<<=============

Alternatively, if the strings are values held on a worksheet, try:

'=============>>
Public Sub Tester2()
Dim rng As Range '

Set rng = Range("A1:A20") '<<==== CHANGE
rng.Replace What:=" ", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub
'<<=============

Of course, in the latter case, you could perform the operation manually
using Edit | Replace.

 

[Dave Peterson]

If you can remove all the spaces, then you can:

select the range
edit|replace
what: (spacebar)
with: (leave blank)
replace all

If you wanted a formula, you could use a helper cell:

=substitute(a1," ","")
to remove all the space characters.

 

[Ron Rosenfeld]

Hello,

I have a text string look like "xxx xxxx", 2 spaces between those x's. I
would like to remove those spaces between the two strings. The left , right
function cannot be used as i have different combination of word between the
two words in the cell.

Please advice.

Thanks much.

If you only want to remove the extra spaces, leaving one, The TRIM worksheet
function will remove extra spaces between words, as well as spaces at the
beginning and end of the string:

=TRIM(A1)

If you want to remove all the spaces, then:

=SUBSTITUTE(A1," ","")

or use the Edit/Replace tool


--ron