Replacing text

kent.robinson

I have a text string that is an IP address ex: xx.xxx.xxx.123 or xx.xxx.xx.1 that i need to add 15 to the last set of digits and create a new ip address. the last set of digits is not always 3 digits. I have tried right/value and substitute with no success

xx.xxx.xxx.123 would create new xx.xxx.xxx.138

xx.xxx.xxx.1 would create new ip of xx.xxx.xxx.16

bisaacs

Hey @kent.robinson,

the preceding x's, does the format/count of them change at all? If not, you can use this:

="xx.xxx.xxx." + (VALUE(MID(Days@row, (FIND(".", Days@row, 8) + 1), 3)) + 15)

bisaacs

Hey @kent.robinson,

This formula should work:

=VALUE(MID([IP Address]@row, (FIND(".", [IP Address]@row, 8) + 1), 3)) + 15

MID/FIND starts at the last period, then parses the last few digits, then it converts the text to a value then adds 15 to the number.

Hope this helps!

kent.robinson

thank you. this gets me the value i want, but now i need add this new number to the digits before that last . any thoughts

bisaacs

Hey @kent.robinson,

the preceding x's, does the format/count of them change at all? If not, you can use this:

="xx.xxx.xxx." + (VALUE(MID(Days@row, (FIND(".", Days@row, 8) + 1), 3)) + 15)