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

 

[jtakw]

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)

That's because you now have a 2 leading digit...

Again, please show MORE data samples.

 

[Snypa]

Okay, I figured out the issue. It expects the first octet to have 3 characters, which is not always the case. Is there anyway this can be fixed in the formula? It is happy to work with 2 or 3 characters in the 2nd and 3rd octets.

 

[Snypa]

That's because you now have a 2 leading digit...

Again, please show MORE data samples.

Yeah, that's my bad. Please, don't get frustrated, I really appreciate your help!

 

[admiral100]

Hi,

Try this : 3 red space

D7 =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(C7,FIND("*",SUBSTITUTE(C7,".","*",{3}))+3),"xxx",220)," "," "),15))

[TABLE="width: 411"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]123.13.123.xxx[/TD]
[TD]123.13.123.220[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]123.132.132.189 (VPN)[/TD]
[TD]123.132.132.189[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]123.32.32.89 RT[/TD]
[TD]123.32.32.89[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1.32.432.189 VPN[/TD]
[TD]1.32.432.189[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D 123.32.32.xxx S 123.32.123.xxx[/TD]
[TD]123.32.32.220[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10.88.25.xxx[/TD]
[TD]10.88.25.220[/TD]
[/TR]
</tbody>[/TABLE]

 

[Snypa]

Thank you guys!

 

[jtakw]

You're welcome.

Hopefully, this will cover all scenarios:

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
6	10.88.25.xxx		10.88.25.220
7	1.1.111.xxx		1.1.111.220
8	D 1.11.111		1.11.111
9	D 12.23.333.xxx		12.23.333.220
Sheet33

Cell Formulas
Range	Formula
C1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,IF(MID(A1,2,1)=" ",3,1),255),"xxx",220)," ",REPT(" ",100)),100))