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

Hello,

This should work.

=SUBSTITUTE(A1," ","")


HTH,
Roger



"ranswert" wrote:

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

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.

"Roger Converse" wrote:

Hello,

This should work.

=SUBSTITUTE(A1," ","")


HTH,
Roger



"ranswert" wrote:

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

On Fri, 11 Jan 2008 06:20:03 -0800, ranswert
wrote:

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

Worked Great
Thanks

"Ron Rosenfeld" wrote:

On Fri, 11 Jan 2008 06:20:03 -0800, ranswert
wrote:

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

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.

ranswert wrote:

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

--

Dave Peterson