How to separate values in a field to new fields?

[atebysandwich]

I have a lookup with a field called IP. The field has values that have multiple IPs in them an I would like to sperate them out each into their own field. Some IPs are separated by colons and some are separated by semicolons, and some fields have 3+ IPs. Regardless, I need the IPs in the field beyond the first one to be in their own Field column named IP2 and IP3, etc. 

What I have:

IP

1.1.1.1,2.2.2.2 

or 

IP 

1.1.1.1;2.2.2.2

I've tried something like the below but the makemv only seems to work for the "," and the seperated IPs still show up in the original IP field. 

| makemv delim=";" allowempty=true IP
| makemv delim="," allowempty=true IP
| mvexpand IP

[richgalloway]

There are multiple ways to do that.  Here's one.

| inputlookup mylookup.csv
``` Normalize the separator ```
| eval IP = replace(IP, ";", ",")
``` Separate the IP addresses ```
| eval IP = split(IP, ",")
``` Give each IP address its own event ```
| mvexpand IP
``` Write the results back to the lookup, if desired ```
``` | outputlookup mylookup.csv ```

[atebysandwich]

This worked up until the mexpand. It normalized the punctuation and spit the IPs, but each IP sis till under the same original field of IP. 

It may have helped if I mentioned the other fields: DNS IP OS. Doing what was suggested, this is what I have currently:

DNS         IP

server1   1.1.1.1

server1    2.2.2.2

I would like to have 2.2.2.2 in a seperate field named IP2. And if the server has more than two IPS, a field named IP3 and so on. 

[richgalloway · Accepted solution]

Sorry, I missed that requirement.  Creating separate fields for each IP address is doable with a fixed number of IPs, but not for an arbitrary number of them.

| inputlookup mylookup.csv
``` Normalize the separator ```
| eval IP = replace(IP, ";", ",")
``` Separate the IP addresses ```
| eval IP = split(IP, ",")
``` Give each IP address its own field ```
| eval IP=mvindex(IP, 0), IP2=mvindex(IP, 1), IP3=mvindex(IP, 2)
| table DNS IP*
``` Write the results back to the lookup, if desired ```
``` | outputlookup mylookup.csv ```

[atebysandwich]

Thank you for your help