Extract IP Address from text/string

S

Snypa

New Member
Joined
Nov 1, 2013
Messages
45
Hello,

I have a vlookup that will return values such as the following:

1. "123.123.123.xxx"
2. "123.32.32.89 (VPN)"
3. "123.32.32.89 RT"
4. "123.32.32.89 VPN"
5. "D 123.32.32.xxx S 123.32.123.xxx"

I currently have the following formula which only works in the first instance: =LEFT(C7,FIND(".",C7,FIND(".",C7,FIND(".",C7)+1)+1)-1)&".220"

What I need is a formula that will extract either the whole IP address in instances 2, 3, 4 or the first 3 octets in the final instance that would also work in the first instance (only need the first IP address).
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Is this what you want?

Excel 2010
CD
7123.123.123.xxx123.123.123.220
8123.32.32.89 (VPN)123.32.32.220
9123.32.32.89 RT123.32.32.220
10123.32.32.89 VPN123.32.32.220
11D 123.32.32.xxx S 123.32.123.xxx123.32.32.220
Sheet1
Cell Formulas
RangeFormula
D7=MID(C7,SEARCH(".",C7)-3,SEARCH(".",C7,SEARCH(".",C7,SEARCH(".",C7)+1)+1)-(SEARCH(".",C7)-3-1))&"220"
D8=MID(C8,SEARCH(".",C8)-3,SEARCH(".",C8,SEARCH(".",C8,SEARCH(".",C8)+1)+1)-(SEARCH(".",C8)-3-1))&"220"
D9=MID(C9,SEARCH(".",C9)-3,SEARCH(".",C9,SEARCH(".",C9,SEARCH(".",C9)+1)+1)-(SEARCH(".",C9)-3-1))&"220"
D10=MID(C10,SEARCH(".",C10)-3,SEARCH(".",C10,SEARCH(".",C10,SEARCH(".",C10)+1)+1)-(SEARCH(".",C10)-3-1))&"220"
D11=MID(C11,SEARCH(".",C11)-3,SEARCH(".",C11,SEARCH(".",C11,SEARCH(".",C11)+1)+1)-(SEARCH(".",C11)-3-1))&"220"
 
Upvote 0
This is great! Thanks.

Just one issue, the formula replaces the forth octet with 220, however, the ones without 'xxx' don't need it replacing. could you change those? (row 8-10)

Scott T said:
Is this what you want?
Excel 2010
CD
123.123.123.xxx123.123.123.220
123.32.32.89 (VPN)123.32.32.220
123.32.32.89 RT123.32.32.220
123.32.32.89 VPN123.32.32.220
D 123.32.32.xxx S 123.32.123.xxx123.32.32.220

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=MID(C7,SEARCH(".",C7)-3,SEARCH(".",C7,SEARCH(".",C7,SEARCH(".",C7)+1)+1)-(SEARCH(".",C7)-3-1))&"220"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=MID(C8,SEARCH(".",C8)-3,SEARCH(".",C8,SEARCH(".",C8,SEARCH(".",C8)+1)+1)-(SEARCH(".",C8)-3-1))&"220"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=MID(C9,SEARCH(".",C9)-3,SEARCH(".",C9,SEARCH(".",C9,SEARCH(".",C9)+1)+1)-(SEARCH(".",C9)-3-1))&"220"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=MID(C10,SEARCH(".",C10)-3,SEARCH(".",C10,SEARCH(".",C10,SEARCH(".",C10)+1)+1)-(SEARCH(".",C10)-3-1))&"220"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=MID(C11,SEARCH(".",C11)-3,SEARCH(".",C11,SEARCH(".",C11,SEARCH(".",C11)+1)+1)-(SEARCH(".",C11)-3-1))&"220"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
So the row 8-10 do not need 220 added?


Excel 2010
CD
7123.123.123.xxx123.123.123.220
8123.32.32.89 (VPN)123.32.32.89
9123.32.32.89 RT123.32.32.89
10123.32.32.89 VPN123.32.32.89
11D 123.32.32.xxx S 123.32.123.xxx123.32.32.220
Sheet5
Cell Formulas
RangeFormula
D7=MID(C7,SEARCH(".",C7)-3,SEARCH(".",C7,SEARCH(".",C7,SEARCH(".",C7)+1)+1)-(SEARCH(".",C7)-3-1))&IF(H7="x","220",MID(C7,G7+1,2))
D8=MID(C8,SEARCH(".",C8)-3,SEARCH(".",C8,SEARCH(".",C8,SEARCH(".",C8)+1)+1)-(SEARCH(".",C8)-3-1))&IF(H8="x","220",MID(C8,G8+1,2))
D9=MID(C9,SEARCH(".",C9)-3,SEARCH(".",C9,SEARCH(".",C9,SEARCH(".",C9)+1)+1)-(SEARCH(".",C9)-3-1))&IF(H9="x","220",MID(C9,G9+1,2))
D10=MID(C10,SEARCH(".",C10)-3,SEARCH(".",C10,SEARCH(".",C10,SEARCH(".",C10)+1)+1)-(SEARCH(".",C10)-3-1))&IF(H10="x","220",MID(C10,G10+1,2))
D11=MID(C11,SEARCH(".",C11)-3,SEARCH(".",C11,SEARCH(".",C11,SEARCH(".",C11)+1)+1)-(SEARCH(".",C11)-3-1))&IF(H11="x","220",MID(C11,G11+1,2))
 
Upvote 0
Hello,

Thanks again for the reply, the first formula does not seem to work. Additionally, it has references to column H and G, is this correct? I changed it all to column C and I got a value error.

Please help :)
 
Upvote 0
Hi,

I think it might be a lot easier for us to help if you show various samples and expected results.
 
Upvote 0
These samples and expected output are correct but the formula does not seem to product them, perhaps a mistype somewhere?

Scott T said:
So the row 8-10 do not need 220 added?

Excel 2010
CD
123.123.123.xxx123.123.123.220
123.32.32.89 (VPN)123.32.32.89
123.32.32.89 RT123.32.32.89
123.32.32.89 VPN123.32.32.89
D 123.32.32.xxx S 123.32.123.xxx123.32.32.220

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]=MID(C7,SEARCH(".",C7)-3,SEARCH(".",C7,SEARCH(".",C7,SEARCH(".",C7)+1)+1)-(SEARCH(".",C7)-3-1))&IF(H7="x","220",MID(C7,G7+1,2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=MID(C8,SEARCH(".",C8)-3,SEARCH(".",C8,SEARCH(".",C8,SEARCH(".",C8)+1)+1)-(SEARCH(".",C8)-3-1))&IF(H8="x","220",MID(C8,G8+1,2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=MID(C9,SEARCH(".",C9)-3,SEARCH(".",C9,SEARCH(".",C9,SEARCH(".",C9)+1)+1)-(SEARCH(".",C9)-3-1))&IF(H9="x","220",MID(C9,G9+1,2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=MID(C10,SEARCH(".",C10)-3,SEARCH(".",C10,SEARCH(".",C10,SEARCH(".",C10)+1)+1)-(SEARCH(".",C10)-3-1))&IF(H10="x","220",MID(C10,G10+1,2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=MID(C11,SEARCH(".",C11)-3,SEARCH(".",C11,SEARCH(".",C11,SEARCH(".",C11)+1)+1)-(SEARCH(".",C11)-3-1))&IF(H11="x","220",MID(C11,G11+1,2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
I'm wishing you would show "more samples with Results" instead of us trying to guess what you need, so without that, see if this works for you:


Book1
ABC
1123.123.123.xxx123.123.123.220
2123.32.32.89 (VPN)123.32.32.89
3123.32.32.89 RT123.32.32.89
4123.32.32.89 VPN123.32.32.89
5D 123.32.32.xxx S 123.32.123.xxx123.32.32.220
Sheet33
Cell Formulas
RangeFormula
C1=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(".",A1)-3,255),"xxx",220)," ",REPT(" ",100)),100))


C1 formula copied down.
 
Upvote 0
That works when I test it on a new worksheet but when I use it within my Spread sheet, I am getting a #Value error. Any idea why, such a number or text format?

Looking at the error its the mid statement that gives the error. It looks correct though:

mid("10.88.25.xxx", 0, 255)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA to filter IP addresses based on first 2 octets
2
Replies
16
Views
2K
addistexan
A
E
  • Question Question
Extracting State Abbreviation from a String
Replies
5
Views
396
FormR
FormR
C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
614
Fluff
Fluff
M
  • Solved
Extracting text between multiple sets of markers and displaying on separate lines
2
Replies
12
Views
950
Fluff
Fluff
ECB
  • Question Question
Finding the second instance with an xlookup
Replies
4
Views
830
ECB
ECB

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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