Insert Spaces (2000)

Topic

New Reply

I have a long list of company names that I need to compare to another file. Unfortunately, in my original list of companies most do not have spaces in the company name. For example, AnnTaylorStores instead of Ann Taylor Stores. As you can imagine, this makes life difficult. Is there a way I can “easily” insert spaces before any Capital Letters and symbols (i.e., &)? I don’t care if this puts in double spaces in the cells that already have spaces as I can do a quick search/replace on double spaces and replace with single space.

Any help is greatly appreciated!!

Eve

Reply | Quote

Replies

Eve

I suggest instead of adding spaces, remove the spaces. make a copy of the data you want to compare, and then remove the spaces and then do the compare. Its easier to remove a space vs add the space because there is no uniform pattern. Like add it in the 5th position, or after a T.

If you need any help in writing the code to strip the spaces let us know.

Wassim

Reply | Quote

I’ve done that for the interim, but I’d really like to add the spaces eventually so we don’t have to remove them each time. Plus it’s easier to read for longer company names.

Thanks!

Reply | Quote

Hi Eve,
The following macro will add spaces before anything that isn’t a small letter (i.e. all capitals, symbols etc.)
You’ll need to replace any double spaces created, as you mentioned.

Sub AddSpaces()
    Dim rngCell As Range, intPos As Integer
    For Each rngCell In Selection
        For intPos = Len(rngCell) To 2 Step -1
            Select Case Asc(Mid(rngCell, intPos, 1))
            Case Is  122
                rngCell = Mid(rngCell, 1, intPos - 1) & " " & Mid(rngCell, intPos)
            Case Else
            End Select
        Next intPos
    Next rngCell
End Sub

Hope that helps.

Reply | Quote

Thanks, Rory — this is very helpful!

As extra credit (ha ha), how would you change it so that it would only put spaces before a cap letter if the letter before it was lower case? This way it wouldn’t put spaces in-between caps (like TRW).

Sorry, I thought of this after.

Reply | Quote

This slight modification to Rory’s code should do what you want:

Sub AddSpaces()
Dim rngCell As Range, intPos As Integer
    For Each rngCell In Selection
        For intPos = Len(rngCell) To 2 Step -1
            Select Case Asc(Mid(rngCell, intPos, 1))
            Case Is  122
                If (Asc(Mid(rngCell, intPos - 1, 1)) >= 97) And (Asc(Mid(rngCell, intPos - 1, 1)) <= 122) Then
                    rngCell = Mid(rngCell, 1, intPos - 1) & " " & Mid(rngCell, intPos)
                End If
            Case Else
            End Select
        Next intPos
    Next rngCell
End Sub

Reply | Quote

Thanks, everyone! You guys rule

Reply | Quote

And a variation on a variation (to deal with things like ABCComputers):

Sub AddSpaces()
    Dim rngCell As Range, intPos As Integer, strText As String
Dim strCheck As String, strCheck2 As String
    For Each rngCell In Selection
        strText = rngCell
        For intPos = Len(strText) To 2 Step -1
            strCheck = Mid(strText, intPos, 1)
            Select Case strCheck
                Case " "
                Case Is = LCase(strCheck)
                Case Else
                    strCheck2 = Mid(strText, intPos + 1, 1)
                    Select Case strCheck2
                        Case " "
                        Case Is = LCase(strCheck2)
                            strText = Mid(strText, 1, intPos - 1) & " " _
& Mid(strText, intPos)
                        Case Else
                            strCheck2 = Mid(strText, intPos - 1, 1)
                            Select Case strCheck2
                                Case " "
                                Case Is = LCase(strCheck2)
                                    strText = Mid(strText, 1, intPos - 1) _
& " " & Mid(strText, intPos)
                                Case Else
                            End Select
                    End Select
            End Select
        Next intPos
        rngCell = strText
    Next rngCell
End Sub

FWIW.

Reply | Quote

Thank you, Rory, however this inserts spaces at odd points in all cap names. For example, in SKW-MBT it will change it to SK W-MB T. To make myself understand and learn this code, I fiddled with it until it came out right. I think it incorporates your code with Legare’s. Here’s what I came up with:

Sub AddSpaces()
Dim rngCell As Range, intPos As Integer, strText As String
Dim strCheck As String, strCheck2 As String
Dim intAsc As Integer, intAsc2 As Integer

For Each rngCell In Selection
strText = rngCell
‘Len counts the number of characters in string
‘intPos will then equal number of characters in string
For intPos = Len(strText) To 2 Step -1
‘Mid returns specified number of characters in string
‘so this will return last character in first loop
strCheck = Mid(strText, intPos, 1)
‘Asc returns the character code
‘”A” = 65, “Z” = 90, “a” = 97, “z” = 122
intAsc = Asc(strCheck)
Select Case Asc(strCheck)
‘if lowercase, then next intPos
Case Is > 96
Case Else
‘if strCheck is uppercase, then strCheck2 will equal character to the right
strCheck2 = Mid(strText, intPos + 1, 1)
‘if last character then will move to next intPos
If strCheck2 “” Then
intAsc2 = Asc(strCheck2)
Select Case Asc(strCheck2)
‘if uppercase then next intPos
Case Is < 97
Case Else
'if lowercase then inserts space
strText = Mid(strText, 1, intPos – 1) & " " _
& Mid(strText, intPos)
End Select
End If
End Select
Next intPos
rngCell = strText
Next rngCell
'Inserts spaces around "-"
Cells.Replace What:="-", Replacement:=" – "
'Inserts spaces around "&"
Cells.Replace What:="&", Replacement:=" & "
'Deletes any double spaces
Cells.Replace What:=" ", Replacement:=" "
End Sub

I did the intAsc so I could watch what was happening in the Locals window — otherwise I couldn't figure out what was going wrong.

Anyway, thank you for your help, everyone, as I feel like I'm finally learning what this "VBA jibberish" means!

Eve

Reply | Quote

Eve,

Both Wassim and Rory presented a possible way to solve your problem. I just want to add some code for two UserDefined Functions, one to remove spaces, and one to add spaces before an uppercase letter. The last function is just a variation of Rory’s subroutine (I hope Rory doesn’t mind?).

Function RemoveSpaces(sText As String) As String
Dim A As Variant
Dim i As Integer
A = Split(sText)
RemoveSpaces = ""
For i = 0 To UBound(A)
  RemoveSpaces = RemoveSpaces & A(i)
Next i
End Function

Function AddSpaces(sText As String) As String
Dim intPos As Integer
  For intPos = Len(sText) To 2 Step -1
    Select Case Asc(Mid(sText, intPos, 1))
      Case Is  122
        sText = Mid(sText, 1, intPos - 1) & " " & Mid(sText, intPos)
      Case Else
    End Select
  Next intPos
AddSpaces = sText
End Function

Reply | Quote