I have several item numbers that have various spaces
1234 5678 A
and I want to strip the spaces and replace with dashes
1234-5678-A
Here's a brute force method:
Public Function OneOrMoreBlanksToHyphen(varIn As Variant) As Variant
Dim strTemp As String
Dim intFirstBlank As Integer
Dim strOut As String
Dim I As Integer
Dim intBlanksSkipped As Integer
OneOrMoreBlanksToHyphen = varIn
If IsNull(varIn) Then Exit Function
strTemp = Trim(CStr(varIn)) 'Trimmed
If Len(strTemp) = 0 Then Exit Function
intFirstBlank = InStr(1, strTemp, " ", vbTextCompare)
If intFirstBlank = 0 Then Exit Function
strTemp = CStr(varIn) 'Untrimmed
I = 1
Do While I <= Len(strTemp)
'Skip initial blanks, but add to output string
Do While MID(strTemp, I, 1) = " " And I <= Len(strTemp)
strOut = strOut & MID(strTemp, I, 1)
I = I + 1
Loop
Do While I <= Len(strTemp)
'Skip nonblanks, but add to output string
Do While MID(strTemp, I, 1) <> " " And I <= Len(strTemp)
strOut = strOut & MID(strTemp, I, 1)
I = I + 1
Loop
'At a blank
'Skip blank and following blanks
intBlanksSkipped = 0
Do While MID(strTemp, I, 1) = " " And I <= Len(strTemp)
I = I + 1
intBlanksSkipped = intBlanksSkipped + 1
Loop
'Only replace the set of blanks if they are not at the end
If I < Len(strTemp) + 1 Then
strOut = strOut & "-"
Else
strOut = strOut & String(intBlanksSkipped, " ")
End If
Loop
Loop
OneOrMoreBlanksToHyphen = strOut
End Function
Note: I only did superficial testing, but it seems to do what you
require.
James A. Fortune
(e-mail address removed)
Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.