Howdy, tricky one this....
Howdy, tricky one this....
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Howdy, tricky one this....

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    I have a column of names, but they are downloaded and each cell with a name in it has a large number of spaces before the name itself. So for example, the data in a cell would look like this:

    " Jimmy"

    Can anyone give me some code that will lok down this column (column G) and remove all the spaces before the names?

    I'd be really grateful for this

    Thanks

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Trim and then the cell reference, removes all extra spaces

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi joe

    Try this

    Code:
    Sub GoAwaySpaces()
    
    Columns(7).EntireColumn.Insert
        With Range("H1", Range("H65536").End(xlUp))
            .Offset(0, -1).FormulaR1C1 = "=TRIM(RC[1])"
            .Offset(0, -1) = .Offset(0, -1).Value
        End With
    Columns(8).EntireColumn.Delete
     
    End Sub

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You can also try this if ALL names have spaces in front. Hilite the column,from the Data Menu, Text to Columns, select Delimited from the first window and Spaces from the second. Note the column immediately to the right must be vacant.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com