Extract single IP address from a string

I

IanPC

New Member
Joined
Jul 3, 2018
Messages
3
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.
 
Sort by date Sort by votes

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
i forgot about ( and ) so
sourcesourceTAD
Gwinnett (3a)_VIPR 172.26.56.104Gwinnett 3a VIPR 172.26.56.104172.26.56.104
Harmonic - 172.24.100.46Harmonic - 172.24.100.46172.24.100.46
Imagine BMS 107.8.2.116Imagine BMS 107.8.2.116107.8.2.116
Imagine Encoder – 50.113.193.109Imagine Encoder – 50.113.193.10950.113.193.109
IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.215IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.21524.174.45.215
Imagine Encoder 1.2.10.54Imagine Encoder 1.2.10.541.2.10.54
Imagine Encoder 107.8.2.116Imagine Encoder 107.8.2.116107.8.2.116
Imagine Encoder 107.8.3.185Imagine Encoder 107.8.3.185107.8.3.185
Imagine Encoder 170.8.2.116Imagine Encoder 170.8.2.116170.8.2.116
Imagine Encoder 24.174.45.215Imagine Encoder 24.174.45.21524.174.45.215
IMAGINE ENCODER 46.9.2IMAGINE ENCODER 46.9.246.9.2
IMAGINE ENCODER 46.9.2IMAGINE ENCODER 46.9.246.9.2
Imagine Encoder TWC-MW-04_10 - 107.8.2.116Imagine Encoder TWC-MW-04 10 - 107.8.2.116107.8.2.116
Imagine Encoder TWC-MW-05 107.8.3.109Imagine Encoder TWC-MW-05 107.8.3.109107.8.3.109
IMG_c7-01 (107.8.2.116)IMG c7-01 107.8.2.116107.8.2.116
IN-PS9K_03 172.16.106.29IN-PS9K 03 172.16.106.29172.16.106.29
IN-PS9K_03_SGHLGA - 172.26.108.182IN-PS9K 03 SGHLGA - 172.26.108.182172.26.108.182
IN-PS9K_05_SLDCMO 172.28.52.73IN-PS9K 05 SLDCMO 172.28.52.73172.28.52.73
ION Encoder 172.21.34.13ION Encoder 172.21.34.13172.21.34.13
ION-4 Encoder - 107.9.92.70ION-4 Encoder - 107.9.92.70107.9.92.70
IVMS 184.152.127.53IVMS 184.152.127.53184.152.127.53
iVMS 184.152.127.53iVMS 184.152.127.53184.152.127.53
IVMS 69.76.65.67IVMS 69.76.65.6769.76.65.67
ivms_KNLC00000NAH_mpeg_8_239.240.0.182ivms KNLC00000NAH mpeg 8 239.240.0.182239.240.0.182

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace1 = Table.ReplaceValue(Source,"_"," ",Replacer.ReplaceText,{"source"}),
    Replace2 = Table.ReplaceValue(Replace1,"(","",Replacer.ReplaceText,{"source"}),
    Replace3 = Table.ReplaceValue(Replace2,")","",Replacer.ReplaceText,{"source"}),
    TAD = Table.AddColumn(Replace3, "TAD", each Text.AfterDelimiter([source], " ", {0, RelativePosition.FromEnd}), type text)
in
    TAD
 
Upvote 0
yeah, I saw that, but didn't want to be such a perfectionist, but thanks again.
 
Upvote 0
I have been away for a while & I note that you have a satisfactory solution but you did earlier indicate that you were interested in a vba solution. In case that still is of interest to you, this user-defined function produces the same results as the power query - at least for the sample data in post #32.

VBA Code: 
Function IP(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "((\d{1,3}\.){1,3}\d{1,3})"
    If .Test(s) Then IP = .Execute(s)(0)
  End With
End Function

Faker4442 1.xlsm
AB
1sourceIP
2Gwinnett (3a)_VIPR 172.26.56.104172.26.56.104
3Harmonic - 172.24.100.46172.24.100.46
4Imagine BMS 107.8.2.116107.8.2.116
5Imagine Encoder – 50.113.193.10950.113.193.109
6IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.21524.174.45.215
7Imagine Encoder 1.2.10.541.2.10.54
8Imagine Encoder 107.8.2.116107.8.2.116
9Imagine Encoder 107.8.3.185107.8.3.185
10Imagine Encoder 170.8.2.116170.8.2.116
11Imagine Encoder 24.174.45.21524.174.45.215
12IMAGINE ENCODER 46.9.246.9.2
13IMAGINE ENCODER 46.9.246.9.2
14Imagine Encoder TWC-MW-04_10 - 107.8.2.116107.8.2.116
15Imagine Encoder TWC-MW-05 107.8.3.109107.8.3.109
16IMG_c7-01 (107.8.2.116)107.8.2.116
17IN-PS9K_03 172.16.106.29172.16.106.29
18IN-PS9K_03_SGHLGA - 172.26.108.182172.26.108.182
19IN-PS9K_05_SLDCMO 172.28.52.73172.28.52.73
20ION Encoder 172.21.34.13172.21.34.13
21ION-4 Encoder - 107.9.92.70107.9.92.70
22IVMS 184.152.127.53184.152.127.53
23iVMS 184.152.127.53184.152.127.53
24IVMS 69.76.65.6769.76.65.67
25ivms_KNLC00000NAH_mpeg_8_239.240.0.182239.240.0.182
Sheet1
Cell Formulas
RangeFormula
B2:B25B2=IP(A2)
 
Upvote 0
Thank you for the additional solution. Always nice to have alternatives.
 
Upvote 0
Hi,

If I want to extract multiple IPs in one string?

Peter_SSs said:
I have been away for a while & I note that you have a satisfactory solution but you did earlier indicate that you were interested in a vba solution. In case that still is of interest to you, this user-defined function produces the same results as the power query - at least for the sample data in post #32.

VBA Code: 
Function IP(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "((\d{1,3}\.){1,3}\d{1,3})"
    If .Test(s) Then IP = .Execute(s)(0)
  End With
End Function

Faker4442 1.xlsm
AB
1sourceIP
2Gwinnett (3a)_VIPR 172.26.56.104172.26.56.104
3Harmonic - 172.24.100.46172.24.100.46
4Imagine BMS 107.8.2.116107.8.2.116
5Imagine Encoder – 50.113.193.10950.113.193.109
6IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.21524.174.45.215
7Imagine Encoder 1.2.10.541.2.10.54
8Imagine Encoder 107.8.2.116107.8.2.116
9Imagine Encoder 107.8.3.185107.8.3.185
10Imagine Encoder 170.8.2.116170.8.2.116
11Imagine Encoder 24.174.45.21524.174.45.215
12IMAGINE ENCODER 46.9.246.9.2
13IMAGINE ENCODER 46.9.246.9.2
14Imagine Encoder TWC-MW-04_10 - 107.8.2.116107.8.2.116
15Imagine Encoder TWC-MW-05 107.8.3.109107.8.3.109
16IMG_c7-01 (107.8.2.116)107.8.2.116
17IN-PS9K_03 172.16.106.29172.16.106.29
18IN-PS9K_03_SGHLGA - 172.26.108.182172.26.108.182
19IN-PS9K_05_SLDCMO 172.28.52.73172.28.52.73
20ION Encoder 172.21.34.13172.21.34.13
21ION-4 Encoder - 107.9.92.70107.9.92.70
22IVMS 184.152.127.53184.152.127.53
23iVMS 184.152.127.53184.152.127.53
24IVMS 69.76.65.6769.76.65.67
25ivms_KNLC00000NAH_mpeg_8_239.240.0.182239.240.0.182
Sheet1
Cell Formulas
RangeFormula
B2:B25B2=IP(A2)
Click to expand...
 
Upvote 0
billtadz said:
Hi,

If I want to extract multiple IPs in one string?
Click to expand...
Welcome to the MrExcel board!

What about you give us some sample data (say 8-12 rows showing the sort of variety that can occur with your data) and the expected results, preferably with XL2BB so that we can copy/paste for testing. That way we would have some idea of what your data is actually like and how you would like the results presented. Otherwise, we are simply guessing. ;)
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

What about you give us some sample data (say 8-12 rows showing the sort of variety that can occur with your data) and the expected results, preferably with XL2BB so that we can copy/paste for testing. That way we would have some idea of what your data is actually like and how you would like the results presented. Otherwise, we are simply guessing. ;)
Click to expand...

Sorry, about that.


sourceFirst IPSecond IP
Gwinnett (3a)_VIPR 172.26.56.1043172.26.56.104
Harmonic - 172.24.100.46 255.255.255.0172.24.100.46255.255.255.0
Imagine BMS 107.8.2.116107.8.2.116
Imagine Encoder – 50.113.193.10950.113.193.109
IMAGINE ENCODER - IMG-DLLATX-ENC04 - 24.174.45.2150424.174.45.215
Imagine Encoder 1.2.10.541.2.10.54
Imagine Encoder 107.8.2.116107.8.2.116
Imagine Encoder 107.8.3.185107.8.3.185
Imagine Encoder 170.8.2.116170.8.2.116
Imagine Encoder 24.174.45.21524.174.45.215
IMAGINE ENCODER 46.9.246.9.2(option to ignore if 3 octets or not full IP only?)
IMAGINE ENCODER 46.9.246.9.2(option to ignore if 3 octets or not full IP only?)
Imagine Encoder TWC-MW-04_10 - 107.8.2.1160410107.8.2.116
Imagine Encoder TWC-MW-05 107.8.3.10905107.8.3.109
IMG_c7-01 (107.8.2.116)701107.8.2.116
IN-PS9K_03 172.16.106.29 0.0.0.255903172.16.106.290.0.0.255
IN-PS9K_03_SGHLGA - 172.26.108.182903172.26.108.182
IN-PS9K_05_SLDCMO 172.28.52.73905172.28.52.73
ION Encoder 172.21.34.13172.21.34.13
ION-4 Encoder - 107.9.92.704107.9.92.70
IVMS 184.152.127.53184.152.127.53
iVMS 184.152.127.53184.152.127.53
IVMS 69.76.65.6769.76.65.67
ivms_KNLC00000NAH_mpeg_8_239.240.0.182000008239.240.0.182
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Extract multiple strings from 1 cell
Replies
6
Views
1K
daveac
D
A
  • Question Question
Extracting multiple numbers from one string with blanks & text in it & put into separate columns
2
Replies
13
Views
2K
DanteAmor
DanteAmor
C
  • Question Question
SKU Generator From Various URLs - Different Syntax
Replies
1
Views
867
censo
C
J
  • Question Question
Extract values, ignore blanks and sort by count, based on name selected from drop down
Replies
7
Views
1K
Eric W
Eric W
F
  • Question Question
How to extract partial string onto another cell if conditions are met?
Replies
4
Views
998
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top