Combine data in 2 cels

[gyagos]

All,
I have been searching and i cannot find the answer. I have many rows on a spreadsheet that have multiple IPs associated. What i need to do is breakout all IPs but still retain the hostname and add the IP after the hostname. These are just a few examples, there are many instances of 5+ IPs on a host

Current Data

Hostname	Ips
"Server-1-a01-name"	"111.22.3333.444","111.22.333.555"
"Server-2-a02-name"	"123.45.678.900","123.45.678.901","123.45.678.902"

What i would like the layout to look like

Hostname

"Server-1-a01-name" - "111.22.333.444"

"Server-1-a01-name" - "111.22.333.555"

"Server-2-a02-name" - "123.45.678.900"

"Server-2-a02-name" - "123.45.678.901"

"Server-2-a02-name" - "123.45.678.902"

 

[awoohaw]

Do you want the quotes?
how about:

It isn't all in one column though.

mr excel questions 25.xlsm
	A	B	C	D	E
1	Hostname	Ips
2	"Server-1-a01-name"	"111.22.3333.444","111.22.333.555"	"Server-1-a01-name"-"111.22.3333.444"	"Server-1-a01-name"-"111.22.333.555"
3	"Server-2-a02-name"	"123.45.678.900","123.45.678.901","123.45.678.902"	"Server-2-a02-name"-"123.45.678.900"	"Server-2-a02-name"-"123.45.678.901"	"Server-2-a02-name"-"123.45.678.902"
4
gyagos

Cell Formulas
Range	Formula
C2:D2,C3:E3	C2	=SUBSTITUTE(TEXTSPLIT(REPLACE(REPT(","& A2,COUNTA(TEXTSPLIT(B2,","))),1,1,""),",")& TEXTSPLIT(B2,","),CHAR(34)&CHAR(34),CHAR(34)&"-"&CHAR(34))
Dynamic array formulas.

 

[gyagos]

Thanks
I would prefer if they were all in same column, but i could probably cut and transpose if needed
I'll try this out later on tonite

 

[Peter_SSs]

You could try like this. If your list is very large, the formula could fail since TEXTJOIN has a character limit of 32,767 characters.

23 04 22.xlsm
	A	B	C	D	E
1	Hostname	Ips
2	"Server-1-a01-name"	"111.22.3333.444","111.22.333.555"		"Server-1-a01-name"	"111.22.3333.444"
3	"Server-2-a02-name"	"123.45.678.900","123.45.678.901","123.45.678.902"		"Server-1-a01-name"	"111.22.333.555"
4				"Server-2-a02-name"	"123.45.678.900"
5				"Server-2-a02-name"	"123.45.678.901"
6				"Server-2-a02-name"	"123.45.678.902"
Split List

Cell Formulas
Range	Formula
D2:E6	D2	=TEXTSPLIT(TEXTJOIN("|",,BYROW(A2:B3,LAMBDA(rw,INDEX(rw,1)&","&SUBSTITUTE(INDEX(rw,2),",","|"&INDEX(rw,1)&",")))),",","|")
Dynamic array formulas.

 

[Peter_SSs]

If your list is very large, the formula could fail since TEXTJOIN has a character limit of 32,767 characters.

If that is a possible problem then you could try this macro instead.

Sub Split_IP_List()
  Dim a As Variant, b As Variant, IPs As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    IPs = Split(a(i, 2), ",")
    For j = 0 To UBound(IPs)
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = IPs(j)
    Next j
  Next i
  Range("D2:E2").Resize(k).Value = b
End Sub

 

[Peter_SSs]

Oops, I missed that results should be in a single column so revised suggestions for both methods.

gyagos.xlsm
	A	B	C	D
1	Hostname	Ips
2	"Server-1-a01-name"	"111.22.3333.444","111.22.333.555"		"Server-1-a01-name" - "111.22.3333.444"
3	"Server-2-a02-name"	"123.45.678.900","123.45.678.901","123.45.678.902"		"Server-1-a01-name" - "111.22.333.555"
4				"Server-2-a02-name" - "123.45.678.900"
5				"Server-2-a02-name" - "123.45.678.901"
6				"Server-2-a02-name" - "123.45.678.902"
Split List

Cell Formulas
Range	Formula
D2:D6	D2	=TEXTSPLIT(TEXTJOIN("|",,BYROW(A2:B3,LAMBDA(rw,INDEX(rw,1)&" - "&SUBSTITUTE(INDEX(rw,2),",","|"&INDEX(rw,1)&" - ")))),,"|")
Dynamic array formulas.

Sub Split_IP_List_v2()
  Dim a As Variant, b As Variant, IPs As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For i = 1 To UBound(a)
    IPs = Split(a(i, 2), ",")
    For j = 0 To UBound(IPs)
      k = k + 1
      b(k, 1) = a(i, 1) & " - " & IPs(j)
    Next j
  Next i
  Range("D2").Resize(k).Value = b
End Sub

 

[gyagos]

Thank you Peter and awoohaw.
i tried the =TEXTSPLIT and it came back with a #CALC error (Text too long)
The Macro worked like a charm.

 

[Peter_SSs]

You're welcome. Glad the macro worked for you. Thanks for the follow-up.