Consulting

Results 1 to 6 of 6

Thread: Need help with loop, concatenate and trim in Excel, VBA

  1. #1
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    5
    Location

    Need help with loop, concatenate and trim in Excel, VBA

    Hi,

    I need some help. I have a long list of items in column A with extra spaces, such as: Samsung ', I'd like to loop thru the entire column A, trim out all the extra spaces, I think application.worksheetFunction.Trim(A1), then concatenate the a word "Equipment", the output result as: "Samsung Equipment" in the same column A.

    I was able to do a single item, but not able to get the loop, which I'm not familiar with.

    Much appreciate it,
    Last edited by cp713a; 01-08-2021 at 02:06 PM. Reason: missing tags

  2. #2
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Attach a sample file. However, the following sample code helps to trim the extra spaces including space with special character code 160
    Select the cells with data in column A

    Sub RemoveSpaces()
    Dim myRange As Range
        Dim myCell As Range
        Set myRange = Selection
        For Each myCell In myRange
            If Not IsEmpty(myCell) Then
                myCell = Application.WorksheetFunction.Trim(WorksheetFunction.Substitute(myCell, Chr(160), "")) & " Equipment"
            End If
        Next myCell
    End Sub

  3. #3
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    5
    Location
    Please see attached file for sample. So, what I'd like to see is in ColA, I have existing data, and I'd like to loop thru and achieved like the Result in ColB, but without dump the data in another column. So, the existing text in ColA, loops thru that entire column, concatenate word 'Equipment', adding single quotes ('Samsung Equipment') like so around the text. Hope this helps and makes sense.
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Select the cells you want to process first, then try running:
    Sub blah()
    For Each cll In Selection.Cells
      x = Split(cll.Value, "'")
      x(0) = x(0) & " Equipment"
      cll.Value = Application.Trim(Join(x, "'"))
    Next cll
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      sheet1.columns(1).replace "  "," "
      sheet1.columns(1).replace "'","Equipment'"
    End Sub

  6. #6
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    5
    Location
    Quote Originally Posted by p45cal View Post
    Select the cells you want to process first, then try running:
    Sub blah()
    For Each cll In Selection.Cells
      x = Split(cll.Value, "'")
      x(0) = x(0) & " Equipment"
      cll.Value = Application.Trim(Join(x, "'"))
    Next cll
    End Sub

    p45cal, this works thank you very much.

Tags for this Thread

Posting Permissions

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