EXCEL: How can I remove spaces?

[mrfritz44]

I would like to start with a blank spreadsheet that uses some technique (macro, function?) to automatically remove spaces from a cell when text is pasted into it. Better yet, I would want to remove instances of double-spaces with nothing. The goal is to automate the process of forming a straight block of text from text that contains a lot of extraneous spaces.

I have this function, but I don't know how to use it:

Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
End Function


Any ideas?

Thanks,

Fred

 

[mrfritz44]

Thank you very much Gavin. That last function is the ticket and the one I'll be using.

Have a good one,

Fred