CODEWITHSUNDEEP
vbaexcel
CP1127
CP1127

Reputation: 35

Concatenate Multiple Columns

I have a sheet that lists multiple values starting in C12 where the first six numbers are the same and the last three are different and are separated by spaces (e.g. 123456 111 222 333 444). I would like to use VBA to combine the values using the first six digits in the cell with however many three digit values there are in that cell (e.g. 123456111, 123456222, 123456333, 123456444). These combined values should each be in their own cell. The number of three digit values varies in each cell. I was thinking that maybe the easiest way would be to split them into columns and then combine them, but I cannot figure this out.

Here is an example:

#DATA#
1. 541259 139 285
2. 452679 245
3. 894623 455 654

#DESIRED RESULT#
1. 541259139
2. 541259285
3. 452679245
4. 894623455
5. 894623654

I was able to separate the values onto a second sheet using the following code:

Sub Split()
Dim totalRows As Long, i As Long, sColNames As String
totalRows = LastRowWithData(Sheet1, "C")
For i = 1 To totalRows
    sColNames = Sheet1.Range("C" & i).value
    Call SplitToColumns(sColNames, " ", Sheet2.Range("A" & i))
Next i
End Sub

I am not sure if this is the best way to do this and I cannot figure out how to join them.

Thank you for your time!

Upvotes: 2

Views: 329

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Here is how I would do it:

Sub SplitMyNum()
    Dim i&, j&
    Dim splt() As String
    Dim rngArr() As Variant
    Dim oWs As Worksheet
    Dim tWs As Worksheet

    Set oWs = Worksheets("Sheet1") 'change to your input
    Set tWs = Worksheets("Sheet2") 'change to your output sheet, may be the same as above

    With oWs
        rngArr = .Range(.Cells(1, 3), .Cells(.Rows.Count, 3).End(xlUp)).Value 'loads all the numbers into an array
    End With
    With tWs
        For i = LBound(rngArr, 1) To UBound(rngArr, 1) 'iterate through the numbers
            splt = Split(rngArr(i, 1), " ") 'Split the numbers on spaces
            For j = LBound(splt) + 1 To UBound(splt) 'iterates through the split values
                'Next line concatenates the first with each set after and puts it in the next available cell in column A.
                .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = splt(LBound(splt)) & splt(j)
            Next j
        Next i
    End With
End Sub

Upvotes: 2

Related Questions