Replacing text
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
-
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
-
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!
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!