Replacing text

Options

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

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 05/24/24 Answer ✓
    Options

    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)

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • kent.robinson
    Options

    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
    bisaacs ✭✭✭✭✭
    edited 05/24/24 Answer ✓
    Options

    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)

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!