CONCATENATE

[vmjan02]

I have the code and is all perfect on if the data is in col e and f, bus as per the table the data will start for E and end to AA, so ned to modify the code to get the right output

Book2
	B	C	D	E	F	G	H	I	J
1	Actual Bundle Ether		OutPut
2	BE10		Gi101/0/0/4 ; Gi102/0/0/4	Gi101/0/0/4 Local Configured 0x8000	Gi102/0/0/4 Local Active 0x8000
3	BE40		Gi101/0/0/13	Gi101/0/0/13 Local Active 0x8000
4	BE50		Te0/2/0/3 ; Te0/3/0/3	Te0/2/0/3 Local Active 0x8000	Te0/3/0/3 Local Active 0x8000
5	BE101		Te0/3/0/9	Te0/3/0/9 Local Active 0x8000
6	BE110		Gi100/0/0/25 ; Gi101/0/0/25	Gi100/0/0/25 Local Active 0x8000	Gi101/0/0/25 Local Active 0x8000
7	BE120		Gi100/0/0/24 ; Gi101/0/0/24	Gi100/0/0/24 Local Active 0x8000	Gi101/0/0/24 Local Active 0x8000
8	BE130		Gi100/0/0/27	Gi100/0/0/27 Local Active 0x8000
9	BE1000		Te0/2/0/18 ; Te0/2/0/19 ; Te0/3/0/18 ; Te0/3/0/19	Te0/2/0/18 Local Active 0x8000	Te0/2/0/19 Local Active 0x8000	Te0/3/0/18 Local Active 0x8000	Te0/3/0/19 Local Active 0x8000
10	BE10		Te0/0/0/6 ; Te0/1/0/6	Te0/0/0/6 Local Active 0x8000	Te0/1/0/6 Local Active 0x8000
11	BE20		Te0/0/0/5 ; Te0/1/0/5	Te0/0/0/5 Local Active 0x8000	Te0/1/0/5 Local Standby 0x8000
12	BE40		Te0/0/0/12 ; Te0/0/1/4 ; Te0/1/0/12 ; Te0/1/1/4	Te0/0/0/12 Local Active 0x8000	Te0/0/1/4 Local Active 0x8000	Te0/1/0/12 Local Active 0x8000	Te0/1/1/4 Local Active 0x8000
13	BE500		Te0/0/0/11 ; Te0/1/0/11 ; Te0/2/0/3 ; Te0/2/0/11 ; Te0/3/0/3 ; Te0/3/0/11	Te0/0/0/11 Local Active 0x8000	Te0/1/0/11 Local Active 0x8000	Te0/2/0/3 Local Active 0x8000	Te0/2/0/11 Local Active 0x8000	Te0/3/0/3 Local Active 0x8000	Te0/3/0/11 Local Active 0x8000
Sheet1

It works only for E and F columns, need to en till the last non blank cells in column B

Sub ExtractAndCombineperfect()


    ' Get values from E2 and F2
    Dim valueE As String
    Dim valueF As String

    valueE = Range("E2").Value
    valueF = Range("F2").Value

    ' Take the first 14 characters from each value
    Dim truncatedValueE As String
    Dim truncatedValueF As String

    truncatedValueE = Left(valueE, 14)
    truncatedValueF = Left(valueF, 14)

    ' Combine truncated values with a semicolon and put the result in G2
    Range("D2").Value = Application.WorksheetFunction.Trim(truncatedValueE & ";" & truncatedValueF)
End Sub

 

[RustamMika]

Hi

vmjan02​

try this for your file:

Sub ExtractAndCombineperfect()
    Set abc = CreateObject("System.Collections.ArrayList")
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        abc.Clear
        For j = 5 To Cells(i, Columns.Count).End(xlToLeft).Column
            arr = Split(Cells(i, j))
            abc.Add CStr(arr(0))
        Next
    Cells(i, 4).Value = Join(abc.ToArray, ";")
    Next
End Sub

 

[RobP]

Or this approach:

Sub test()

For Row_num = 2 To ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row ' find the last row of data in Col B

    For Col_no = 5 To 27  '(count from Col E to Col AA)
        
        If Cells(Row_num, Col_no) = "" Then Exit For  'go to next row if col is empty (to save time)
        mydata = mydata & Left(Cells(Row_num, Col_no), InStr(Cells(Row_num, Col_no), " ")) & " ; "
        
    Next Col_no
    
    Range("A" & Row_num).Value = mydata
    mydata = "" 'reset mydata to blank for next row
Next Row_num

End Sub

 

[Fluff]

@RustamMika you might want to test your code before posting.

 

[RobP]

oops, just realised I had mine going into Col A, rather than Col D like you asked : Amended version here:

Sub test()

For Row_num = 2 To ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row ' find the last row of data in Col B

    For Col_no = 5 To 27  '(count from Col E to Col AA)
        
        If Cells(Row_num, Col_no) = "" Then Exit For  'go to next row if col is empty (to save time)
        mydata = mydata & Left(Cells(Row_num, Col_no), InStr(Cells(Row_num, Col_no), " ")) & " ; "
        
    Next Col_no
    
    Range("D" & Row_num).Value = mydata
    mydata = "" 'reset mydata to blank for next row
Next Row_num

End Sub

 

[Fluff]

@RobP you might want to test your code as well.

 

[RobP]

Seems to work on the test data I had. Albeit with an additional ";" on the end of the data rows, but I'll wait to see if thats acceptable or not ..

if you've spotted something else somehow, please feel free to comment more

cheers
Rob

 

[Fluff]

I used the code that puts the data in col A & get

Fluff.xlsm
	A
1
2	; ;
3	;
4	; ;
5	;
6	; ;
7	; ;
8	;
9	; ; ; ;
10	; ;
11	; ;
12	; ; ; ;
13	; ; ; ; ; ;
Sheet6

 

[RobP]

why would you do that .. ?

Just to clean up .. :

Sub test()

For Row_num = 2 To ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row ' find the last row of data in Col B

    For Col_no = 5 To 27  '(count from Col E to Col AA)
        
        If Cells(Row_num, Col_no) = "" Then Exit For  'go to next row if col is empty (to save time)
        mydata = mydata & Left(Cells(Row_num, Col_no), InStr(Cells(Row_num, Col_no), " ")) & "; "
        
    Next Col_no
    
    Range("D" & Row_num).Value = Left(mydata, Len(mydata) - 3)
    mydata = "" 'reset mydata to blank for next row
Next Row_num

End Sub

 

[Fluff]

The OP's data has spaces at the start of the each string so your code is effectively LEFT(cell,1)