Extract IP Address from text/string

[Snypa]

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).

 

[Scott T]

Is this what you want?

Excel 2010
	C	D
7	123.123.123.xxx	123.123.123.220
8	123.32.32.89 (VPN)	123.32.32.220
9	123.32.32.89 RT	123.32.32.220
10	123.32.32.89 VPN	123.32.32.220
11	D 123.32.32.xxx S 123.32.123.xxx	123.32.32.220
Sheet1

Cell Formulas
Range	Formula
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"

 

[Snypa]

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)

Is this what you want?
Excel 2010

	C	D
7	123.123.123.xxx	123.123.123.220
8	123.32.32.89 (VPN)	123.32.32.220
9	123.32.32.89 RT	123.32.32.220
10	123.32.32.89 VPN	123.32.32.220
11	D 123.32.32.xxx S 123.32.123.xxx	123.32.32.220

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Snypa]

Also, the first one does not seem to work.

 

[Scott T]

So the row 8-10 do not need 220 added?

Excel 2010
	C	D
7	123.123.123.xxx	123.123.123.220
8	123.32.32.89 (VPN)	123.32.32.89
9	123.32.32.89 RT	123.32.32.89
10	123.32.32.89 VPN	123.32.32.89
11	D 123.32.32.xxx S 123.32.123.xxx	123.32.32.220
Sheet5

Cell Formulas
Range	Formula
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))

 

[Snypa]

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

 

[jtakw]

Hi,

I think it might be a lot easier for us to help if you show various samples and expected results.

 

[Snypa]

These samples and expected output are correct but the formula does not seem to product them, perhaps a mistype somewhere?

So the row 8-10 do not need 220 added?

Excel 2010

	C	D
7	123.123.123.xxx	123.123.123.220
8	123.32.32.89 (VPN)	123.32.32.89
9	123.32.32.89 RT	123.32.32.89
10	123.32.32.89 VPN	123.32.32.89
11	D 123.32.32.xxx S 123.32.123.xxx	123.32.32.220

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

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
	A	B	C
1	123.123.123.xxx		123.123.123.220
2	123.32.32.89 (VPN)		123.32.32.89
3	123.32.32.89 RT		123.32.32.89
4	123.32.32.89 VPN		123.32.32.89
5	D 123.32.32.xxx S 123.32.123.xxx		123.32.32.220
Sheet33

Cell Formulas
Range	Formula
C1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(".",A1)-3,255),"xxx",220)," ",REPT(" ",100)),100))

C1 formula copied down.

 

[Snypa]

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)