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]

work. Did you test it?

yes i tested both my codes .All working w/o problem.

 

[Fluff]

In that case you copied the data incorrectly, as it does not work on the OP's data, it has the same problem as RobP's original code.