Remove _ inside of string

[ranswert]

Is there a function that will remove the space inside a string? For example
change "string 1" to "string1".
Thanks

 

[Roger Converse]

Hello,

This should work.

=SUBSTITUTE(A1," ","")


HTH,
Roger

 

[ranswert]

I tried this

Private Sub CommandButton1_Click()
Dim selval As String
Dim itemno As String
selval = ComboBox1.Value
MsgBox (selval)
itemno = Substitute(selval, " ", "")
MsgBox (itemno)

End Sub

but I got an error.

 

[Ron Rosenfeld]

I tried this

Private Sub CommandButton1_Click()
Dim selval As String
Dim itemno As String
selval = ComboBox1.Value
MsgBox (selval)
itemno = Substitute(selval, " ", "")
MsgBox (itemno)

End Sub

but I got an error.

It would have been much more helpful had you told us what KIND of error you
got!

As a guess, I'll guess your error was "Sub or Function not defined" with
"Substitute" being highlighted.

VBA does NOT have a Substitute function. The formula Roger gave you was for a
worksheet. In VBA you will need to use the Replace function.

itemno = Replace(selval, " ", "")

Does this help?
--ron

 

[ranswert]

Worked Great
Thanks

It would have been much more helpful had you told us what KIND of error you
got!

As a guess, I'll guess your error was "Sub or Function not defined" with
"Substitute" being highlighted.

VBA does NOT have a Substitute function. The formula Roger gave you was for a
worksheet. In VBA you will need to use the Replace function.

itemno = Replace(selval, " ", "")

Does this help?
--ron

 

[Dave Peterson]

If you have lots to do,

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

You can record a macro to see the code.