Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Spaces (2000)

    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

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Insert Spaces (2000)

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insert Spaces (2000)

    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.
    <pre>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 < 97, Is > 122
    rngCell = Mid(rngCell, 1, intPos - 1) & " " & Mid(rngCell, intPos)
    Case Else
    End Select
    Next intPos
    Next rngCell
    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

    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!

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

    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?).

    <pre>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
    </pre>


    <pre>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 < 97, Is > 122
    sText = Mid(sText, 1, intPos - 1) & " " & Mid(sText, intPos)
    Case Else
    End Select
    Next intPos
    AddSpaces = sText
    End Function
    </pre>


  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

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

    <pre>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 < 97, 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
    </pre>

    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

    Thanks, everyone! You guys rule <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insert Spaces (2000)

    And a variation on a variation (to deal with things like ABCComputers):
    <pre>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
    </pre>

    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Spaces (2000)

    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! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Eve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •