0

Data Source in Cell

A1 192.168.1.2,123.12.123.12,192.168.123.1,123.12.1.1,123.1.1.1

Separate this string by it's comma into each cell

  1. using a formula
  2. Not Using VBA

how do you separate this string by its comma in to each cell? Not using VB Script.

ex.

192.168.1.2,123.12.123.12,192.168.123.1,123.12.1.1,123.1.1.1
192.168.1.2,123.12.123.12
192.168.1.2

Results

B             C               D               E            F
192.168.1.2   123.12.123.12   192.168.123.1   123.12.1.1   123.1.1.1
192.168.1.2   123.12.123.12
192.168.1.2
JvdV
  • 70,606
  • 8
  • 39
  • 70
Lacer
  • 5,668
  • 10
  • 33
  • 45
  • Have a look over [here](https://stackoverflow.com/q/61837696/9758194) and see what `FILTERXML()` can do for you. For example, if one has Excel365, in `B1` use `=TRANSPOSE(FILTERXML(""&SUBSTITUTE(A1,",","")&"","//s"))` – JvdV May 08 '21 at 14:40

1 Answers1

0

A2 = any of these

192.168.1.2,123.12.123.12,192.168.123.1,123.12.1.1,123.1.1.1
192.168.1.2,123.12.123.12
192.168.1.2

This is the comma enumeration cells. Handles all any variation of commas 1-5. Add more as needed.

c1 =1 
D1 =2
E1 =3
F1 =4
G1 =5

B2 = (copy across the cells below the corresponding column enumeration cells.

=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(C$1-1)*LEN($A2)+1,LEN($A2)))
Lacer
  • 5,668
  • 10
  • 33
  • 45