1st 3 octets get subnet translated

[bharath18091991]

Anything within the 10.192.0.0 /13 subnet, the 1st 3 octets get subnet translated to 192.168.0

• Subnets from 10.192.0 to 10.199.255 get translated to 192.168.0
• Examples
o 10.192.0.5  192.168.0.5
o 10.192.0.55  192.168.0.55
o 10.199.255.5  192.168.0.5
o 10.199.255.55  192.168.0.55

tried to perform above task using substitute but as there are two many condition it haven't helped any suggestion folks

[:) Sixthsense :)]

In A1 Cell
10.199.255.55


In B1 Cell

=IFERROR("192.168.0."&MID(A1,FIND("^",SUBSTITUTE(A1,".","^",3))+1,3),A1)

[Special-K]

This works

=IF(AND(RIGHT("00"&TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),(1-1)*50+1,50)),3)*10^3+RIGHT("00"&TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),(2-1)*50+1,50)),3)>=10192,RIGHT("00"&TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),(1-1)*50+1,50)),3)*10^3+RIGHT("00"&TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),(2-1)*50+1,50)),3)<=10199),"192.168.0."&(RIGHT("00"&TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),(4-1)*50+1,50)),3)+0),A1)

Takes each part of the IP address, converts to a 3 digit number, effectively returns one long number and just does a numeric comparison on the first 6 digits


UPDATE: Tsk! I feel so stoopid now looking at Sixthsense's incredibly short solution!

[jindon]

Try

1) Select range
2) Ctrl + H (Find & Replace)
3) Enter 10.19?.*. in find what
4) Enter 192.168.0. in replace with
5) click on Replace All

[bharath18091991]

thanks for response this command is replacing new details on all series all conditions are not getting fulfilled

[bharath18091991]

This Worked thanks Special-K

[bharath18091991]

thanks very easy fix