Transferring IP Ranges into IP Addresses by Excel

Hello Everyone, Today we are here with you for solutions for everyday activities for IP Ranges → Individual IPs

You have to use the below query in Excel you can achieve this by following the steps:-

1. You have to paste your IP ranges in one column of the Excel sheet.
2. Now, You have to select all the cells that contain IP ranges.
3. Navigate in the Excel menu bar for → DATA → “from table”(click)

4. Now you will get one pop-up for creating a table, as shown below.

5. Once, you hit “OK”. You will see a another pop-windows then you have to load your query which I mentioned below.

6. Once, you hit “Advanced Editor”. Then you have to load the below query: → “let
Source = Excel.CurrentWorkbook(){[Name=”Table5"]}[Content],
splitIPRange = Table.SplitColumn(Source, “Data”, Splitter.SplitTextByDelimiter(“-”, QuoteStyle.None), {“IPSplit1”, “IPSplit2”}),
ip1Copy = Table.DuplicateColumn(splitIPRange, “IPSplit1”, “IP1Copy”),
ip2Copy = Table.DuplicateColumn(ip1Copy, “IPSplit2”, “IP2Copy”),
split2 = Table.SplitColumn(ip2Copy, “IP1Copy”, Splitter.SplitTextByEachDelimiter({“.”}, QuoteStyle.None, true), {“IPpart1”, “IPStartNum”}),
split3 = Table.SplitColumn(split2, “IP2Copy”, Splitter.SplitTextByEachDelimiter({“.”}, QuoteStyle.None, true), {“IPpart12”, “IPEndNum”}),
remove1 = Table.RemoveColumns(split3,{“IPpart12”}),
replace1= Table.ReplaceValue(remove1,null, each _[IPStartNum],Replacer.ReplaceValue,{“IPEndNum”}),
typechange = Table.TransformColumnTypes(replace1,{{“IPStartNum”, Int64.Type}, {“IPEndNum”, Int64.Type}}),
listnums = Table.AddColumn(typechange,”CustomList”,each List.Numbers([IPStartNum],[IPEndNum]-[IPStartNum]+1)),
expandList = Table.ExpandListColumn(listnums, “CustomList”),
mergeip = Table.CombineColumns(Table.TransformColumnTypes(expandList, {{“CustomList”, type text}}, “bg-BG”),{“IPpart1”, “CustomList”},Combiner.CombineTextByDelimiter(“.”, QuoteStyle.None),”IPList”),
cleanup = Table.RemoveColumns(mergeip,{“IPSplit1”, “IPSplit2”, “IPStartNum”, “IPEndNum”})
in
cleanup”

7. There you have to change the things that I have highlighted.

8. These highlighted keywords are case-sensitive, please be sure you have replaced them with the same you have mentioned in your sheet.

1. Table5 → you will get this value once you try to load your query from the below screenshot.

2. Data → That should be your column header where you have pasted your IP Ranges.

This will reduce your time more than you are utilizing as of now.

To be continued,

Towards learning, we will meet you in another story.