Extract single IP address from a string

[IanPC]

Hello - I have a spreadsheet of URLs, some of which contain IP addresses and some don't. I'm struggling to write an excel formula that will identify the URLs with IP addresses in them, and extract that IP address into another cell. What formula would I paste down column B to achieve this?


[TABLE="width: 500"]
<tbody>[TR]
[TD]URL (column A)[/TD]
[TD]Desired output (column B)[/TD]
[/TR]
[TR]
[TD]http://sample.domain/the/eed/dhgg.html[/TD]
[TD][#Error of some kind][/TD]
[/TR]
[TR]
[TD]http://10.22.88.11/eid/tedld/ed.html[/TD]
[TD]10.22.88.11[/TD]
[/TR]
[TR]
[TD]http://242.25.23.7/ede/vkjd/tt.html[/TD]
[TD]242.25.23.7[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help gratefully received. Many thanks.

 

[Faker4442]

Sandy,

Thank you so much for the quick response. After learning about how power query works, I was able to implement this to some success. However, if the source field contains any other numbers in the value, those numbers get extracted as well. Is there a way to eliminate the extra numbers or will I just have to manually remove those from the output?

Example:

source	IP
programvideoqoe_sentry_sugarhill_86 (172.24.164.155)_TVONE0000NSS_17_E1079-71	86172.24.164.155000017107971

 

[sandy666]

sorry mate it seems your original example was not representative so post representative example with more than one row
or
use another solutions

 

[Faker4442]

My apologies on my original example not being fully representative. Here is an example of about 25 rows:

source	IP
Gwinnett (3a)_VIPR 172.26.56.104	3172.26.56.104
Harmonic - 172.24.100.46	172.24.100.46
Imagine BMS 107.8.2.116	107.8.2.116
Imagine Encoder – 50.113.193.109	50.113.193.109
IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.215	0424.174.45.215
Imagine Encoder 1.2.10.54	1.2.10.54
Imagine Encoder 107.8.2.116	107.8.2.116
Imagine Encoder 107.8.3.185	107.8.3.185
Imagine Encoder 170.8.2.116	170.8.2.116
Imagine Encoder 24.174.45.215	24.174.45.215
IMAGINE ENCODER 46.9.2	46.9.2
IMAGINE ENCODER 46.9.2	46.9.2
Imagine Encoder TWC-MW-04_10 - 107.8.2.116	0410107.8.2.116
Imagine Encoder TWC-MW-05 107.8.3.109	05107.8.3.109
IMG_c7-01 (107.8.2.116)	701107.8.2.116
IN-PS9K_03 172.16.106.29	903172.16.106.29
IN-PS9K_03_SGHLGA - 172.26.108.182	903172.26.108.182
IN-PS9K_05_SLDCMO 172.28.52.73	905172.28.52.73
ION Encoder 172.21.34.13	172.21.34.13
ION-4 Encoder - 107.9.92.70	4107.9.92.70
IVMS 184.152.127.53	184.152.127.53
iVMS 184.152.127.53	184.152.127.53
IVMS 69.76.65.67	69.76.65.67
ivms_KNLC00000NAH_mpeg_8_239.240.0.182	000008239.240.0.182

 

[sandy666]

this is IP?
000008239.240.0.182
0410107.8.2.116
903172.26.108.182
this is even not IPv6

 

[Faker4442]

You are correct, those ones are not IPs because the query is pulling the other numbers in the value and including it in the output. The value on the left is the source for the query and the output from the query is on the right.

Output from the query:
000008239.240.0.182
0410107.8.2.116
903172.26.108.182

What I need the output from the query to be:

239.240.0.182
107.8.2.116
172.26.108.182

 

[sandy666]

something like this?

source	TAD
Gwinnett (3a) VIPR 172.26.56.104	172.26.56.104
Harmonic - 172.24.100.46	172.24.100.46
Imagine BMS 107.8.2.116	107.8.2.116
Imagine Encoder – 50.113.193.109	50.113.193.109
IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.215	24.174.45.215
Imagine Encoder 1.2.10.54	1.2.10.54
Imagine Encoder 107.8.2.116	107.8.2.116
Imagine Encoder 107.8.3.185	107.8.3.185
Imagine Encoder 170.8.2.116	170.8.2.116
Imagine Encoder 24.174.45.215	24.174.45.215
IMAGINE ENCODER 46.9.2	46.9.2
IMAGINE ENCODER 46.9.2	46.9.2
Imagine Encoder TWC-MW-04 10 - 107.8.2.116	107.8.2.116
Imagine Encoder TWC-MW-05 107.8.3.109	107.8.3.109
IMG c7-01 (107.8.2.116)	(107.8.2.116)
IN-PS9K 03 172.16.106.29	172.16.106.29
IN-PS9K 03 SGHLGA - 172.26.108.182	172.26.108.182
IN-PS9K 05 SLDCMO 172.28.52.73	172.28.52.73
ION Encoder 172.21.34.13	172.21.34.13
ION-4 Encoder - 107.9.92.70	107.9.92.70
IVMS 184.152.127.53	184.152.127.53
iVMS 184.152.127.53	184.152.127.53
IVMS 69.76.65.67	69.76.65.67
ivms KNLC00000NAH mpeg 8 239.240.0.182	239.240.0.182

 

[Faker4442]

exactly

 

[sandy666]

exactly

sure, here is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Source,"_"," ",Replacer.ReplaceText,{"source"}),
    TAD = Table.AddColumn(Replace, "TAD", each Text.AfterDelimiter([source], " ", {0, RelativePosition.FromEnd}), type text)
in
    TAD

 

[Faker4442]

Perfect, thanks so much for all the help!!

 

[sandy666]

You are welcome & thanks for the feedback
sometimes it's nice to see if someone appreciate your work so hit Like in the posts which have helped you