Replace text after nth occurance of character?

[buschman007]

I have a list of IP addresses:


10.9.8.14
10.9.8.24
10.9.8.17
10.9.8.56
10.115.4.3
10.115.4.16
10.115.4.88
10.24.2.132
10.24.2.55

I am looking for a formula to take this column and generate the following in the next column:

10.9.8.0
10.9.8.0
10.9.8.0
10.9.8.0
10.115.4.0
10.115.4.0
10.115.4.0
10.24.2.0
10.24.2.0



The best I have gotten thus far is this:

=REPLACE(H3,10,FIND(".",H3)+2,"0")

However, this only works for longer IPs. Shorter ones don't seem to work. I can adjust the "10" to a shorter number, which will work for shorter IPs. however then longer IPs will replace everything after the 2nd '.'

This formula isn't actually counting out the third dot and replacing the number after said dot with 0, which is what I'm after. I have been scouring around and have not found what I am looking for. Any help would be appreciated.

Thanks,
Mike

 

[jtakw]

Hi,

Will this word for you:

Book1
	A	B	C
1	10.9.8.14		10.9.8.0
2	10.9.8.24		10.9.8.0
3	10.9.8.17		10.9.8.0
4	10.9.8.56		10.9.8.0
5	10.115.4.3		10.115.4.0
6	10.115.4.16		10.115.4.0
7	10.115.4.88		10.115.4.0
8	10.24.2.132		10.24.2.0
9	10.24.2.55		10.24.2.0
10	10.1.2		10.1.0
11	10.2		10.0
12	10.1.2.3.4.5.6.7.8.9		10.1.2.3.4.5.6.7.8.0
Sheet68

Cell Formulas
Range	Formula
C1	=TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",200),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),100))&".0"

Formula copied down.

 

[Tetra201]

Here is a shorter formula to try:

=REPLACE(A1,FIND("@",SUBSTITUTE(A1,".","@",3)),4,".0")

 

[jtakw]

I have a list of IP addresses:

However, this only works for longer IPs. Shorter ones don't seem to work. I can adjust the "10" to a shorter number, which will work for shorter IPs. however then longer IPs will replace everything after the 2nd '.'

This formula isn't actually counting out the third dot and replacing the number after said dot with 0, which is what I'm after. I have been scouring around and have not found what I am looking for. Any help would be appreciated.

Thanks,
Mike

Reason for my longer formula is to accommodate "short" and "long" IP addresses, as shown in my sample Table in Post #2 .

 

[Tetra201]

IPv4 addresses have four byte-size values separated by dots. So, "longer IP's" probably mean something like "192.168.255.111" and "shorter ones" -- like "1.0.0.1"

 

[buschman007]

Tetra, you are spot on with your assessment of what I meant by long and short IPv4s. Thank you for your simple code.

If it's not too much trouble, help me understand your code.

REPLACE(old_text,start_num<num_chars,new_text)
FIND(find_text,winthin_text.[start_num])
SUBSTITUTE(text,old_text,new_text,[instance_num])


=REPLACE(A1,FIND("@",SUBSTITUTE(A1,".","@",3)),4,".0")

SUBSTITUTE(text,old_text,new_text,[instance_num])
SUBSTITUTE(A1,".","@",3)
So you are replacing the 3rd "." with an "@"

FIND(find_text,winthin_text,[start_num])
FIND("@",SUBSTITUTE(A1,".","@",3))
Here you are finding the location of the "@" (Not really sure I understand why this part is needed...)


REPLACE(old_text,start_num,num_chars,new_text)
REPLACE(A1,FIND("@",SUBSTITUTE(A1,".","@",3)),4,".0")
The old text is the IP address in A1, the start_num should return the "@"from the above find (Not sure I grasp why the purpose of start_num), not sure what the 4 is doing here...(replacing the next 4 characters?), but you then replace the @ in 10.10.10@10 with ".0". You last octet could be as short as .1 and as long as .111. So the 4 perhaps is to cover your longest possible number?

Let me know if I'm off base in any of this. Still not sure I understand why the FIND piece is needed.

Thank you all for your help with this. I have what I need to move forward, but look forward to feedback so I can better understand how to build these more complicated Excel formulas. Love making magic happen in Excel.

Thanks,
Mike

 

[Tetra201]

Mike,

You have correctly dissected the formula. The logic behind it is as follows:

1) SUBSTITUTE an "@" for the 3rd dot in the IP address string;
2) FIND the position of the "@" in the string -- it can be any number from 6 to 12;
3) REPLACE part of the IP address from the found position to the end of the string with a ".0"
Note: the maximal length of the part to be replaced is 4 symbols. If there are fewer than 4, Excel will replace only those.

Hope this helps.