Split Cell content

[Funtooskool]

Hi,

I am looking for some assistance to split a cell content which are separated by comma's into respective columns. Though I manage to get that partially work but another hand would be helpful.

[rcm]

please see if this macro works for you..

Please Login or Register  to view this content.

[Funtooskool]

Hey rcm,

Thanks for the response, you are quite close to what I am looking for however the first column 'Ref#' should also reflect in respecting columns.

expected output

Ref# Source Destination
1 1.1.1.1 3.3.3.3
1 2.2.2.2 3.3.3.3
2 4.4.4.4 5.5.5.5
2 4.4.4.4 6.6.6.6
3 7.7.7.7 8.8.8.8
3 7.7.7.7 9.9.9.9
3 7.7.7.7 10.10.10.10

Cheers
FUN

[Funtooskool]

Hi,

Its seems number of rows are hard coded, that was just sample data, there could be many rows of data like that.

Cheers
Shaikh

[JohnTopley]

Updated to show Ref#

Please Login or Register  to view this content.

[Funtooskool]

Hey JohnTopley

that work !! would you be able to incorporate additional column as below

Rule Ref # Source IP Destination IP Port
1 | "1.1.1.1,
| 2.2.2.2," 3.3.3.3 10065, 12050, 12051
2 | 4.4.4.4 "5.5.5.5,
6.6.6.6" 30001, 32002



Expected output
Rule Ref # Source IP Destination IP Port
1 1.1.1.1 3.3.3.3 10065
1 1.1.1.1 3.3.3.3 12050
1 1.1.1.1 3.3.3.3 12051
1 2.2.2.2 3.3.3.3 10065
1 2.2.2.2 3.3.3.3 12050
1 2.2.2.2 3.3.3.3 12051
2 4.4.4.4 5.5.5.5 30001
2 4.4.4.4 5.5.5.5 30001
2 4.4.4.4 6.6.6.6 32002
2 4.4.4.4 6.6.6.6 32002

[JohnTopley]

try this ..

Please Login or Register  to view this content.

[frognz]

Hi,

Have you thought at using Power Query, an add-on to Excel? It is the ideal tool to deal with that type of problems.
It has a tool to split a column into many separate columns by specifying a delimiter. You can do this at a specific occurence of the delimeter or all occurences.

Below the details of it all. It looks complex, best is perhaps to print and just follow the instructions. It is actually really simple once you understand how Power Query works. Before you know it, you could get addicted .

In a nutshell, search the web on how to install Power Query (it is free)

Highlight your data (range A1:C4)
On the Power Query tab that is now in Excel, click the "From Table button". This opens the editor

Select the "source" column, then, on the Home tab, click the "Split By" button. Select comma as the delimiter and "at each occurence of the delimiter"
This creates a second "source" column.

We now unpivot the two. Select the two source columns, on the Transform tab, click "Unpivot columns". Remove the column called "Attributes" (right click and remove). Rename the column "Value" into "Source"

Now select the destination column and do a split by again, comma as delimeter, at each occurence. It will create as many columns as separators.

Now we need to unpivot the "destination" columns. Since we do not know how many there could potentially be, we do the unpivot slightly differently. We do NOt select the "destination" columns, instead, select the other two columns which should be "Ref" and "source". Then go to Transform Tab, open the drop-down of the unpivot columns tool and select the option "unpivot all othr columns"

Some more cleaning: remove the attribute column which is not needed anymore and rename the "value" column into "destination".

Home tab --> save and load.

And that's it! You have just created a query that you can easily reuse next time you have to do this job again. It will also teach you some basics of this jewel tool that is Power Query

Cheers,
Christine

[Funtooskool]

Thanks JohnToPlay,