Formatting IP Addresses

Brad Shapiro

Hello,

I am trying to import an Excel spreadsheet that has a formula to format a column of IP addresses based on the column to the left of the formula. It works in Excel but it appears that SmartSheet does not have the same "Text" function option -- the ability to establish the format of the string.

Trying to take 10.60.2.113 in one cell and have it create a new cell in another column with 010.060.002.113 so that this column will sort the addresses 'properly'

Bassam Khalil

Hi @Brad Shapiro

i prepared a sample sheet for your case and create the required formula please check the following and you can prepare the formula for the remaining column using the concept i used:

1- Formatted IP Formula =JOIN([11]@row:[44]@row, ".")

2- column 1 Formula =VALUE(MID([Original IP]@row, 1, 3))

3- Column 2 Formula =IF([1]@row < 10, VALUE(MID([Original IP]@row, 3, 3)), IF([1]@row < 100, VALUE(MID([Original IP]@row, 4, 3)), IF([1]@row > 100, VALUE(MID([Original IP]@row, 5, 3)), "")))

4- Column 3 Formula =IF(AND([2]@row > 100, [1]@row > 100), (VALUE(MID([Original IP]@row, 9, 3))), IF(AND([2]@row > 100, [1]@row < 10), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row < 10, [1]@row > 100), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row > 100, [1]@row < 100, [1]@row > 10), (VALUE(MID([Original IP]@row, 8, 3))), IF(AND([1]@row > 100, [2]@row < 100, [2]@row > 10), (VALUE(MID([Original IP]@row, 8, 3))), IF(AND([2]@row > 10, [1]@row > 10, [1]@row < 100, [2]@row < 100), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row > 10, [1]@row < 10), (VALUE(MID([Original IP]@row, 6, 3))), IF(AND([2]@row < 10, [1]@row > 10), (VALUE(MID([Original IP]@row, 6, 3))), IF(AND([2]@row < 10, [1]@row < 10), (VALUE(MID([Original IP]@row, 5, 3))), ""))))))))) maybe you find some possibility need to becovered in this formula but you can coverit with same way i did by usin if function with (And ,Or).

5- Column 4 Formula you can prepare it in the same concept as i did for column 3 to cover al possibility ( it need time )

6- Column 11 Formula =IF([1]@row < 10, "00" + LEFT([1]@row, 1), IF(AND([1]@row > 10, [1]@row < 100), "0" + LEFT([1]@row, 2), IF([1]@row >= 100, [1]@row, "")))

7- Column 22 Formula =IF([2]1 < 10, "00" + LEFT([2]1, 1), IF(AND([2]1 > 10, [2]1 < 100), "0" + LEFT([2]1, 2), IF([2]1 >= 100, [2]1, "")))

and you can create the formula for column 33 & 44 using te same concept.

Bassam Khalil

Hi @Brad Shapiro 

Hope you are fine if you can supply a screenshot show number of the IP addresses you need to format .

Bassam Khalil

Hi @Brad Shapiro

i prepared a sample sheet for your case and create the required formula please check the following and you can prepare the formula for the remaining column using the concept i used:

1- Formatted IP Formula =JOIN([11]@row:[44]@row, ".")

2- column 1 Formula =VALUE(MID([Original IP]@row, 1, 3))

3- Column 2 Formula =IF([1]@row < 10, VALUE(MID([Original IP]@row, 3, 3)), IF([1]@row < 100, VALUE(MID([Original IP]@row, 4, 3)), IF([1]@row > 100, VALUE(MID([Original IP]@row, 5, 3)), "")))

4- Column 3 Formula =IF(AND([2]@row > 100, [1]@row > 100), (VALUE(MID([Original IP]@row, 9, 3))), IF(AND([2]@row > 100, [1]@row < 10), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row < 10, [1]@row > 100), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row > 100, [1]@row < 100, [1]@row > 10), (VALUE(MID([Original IP]@row, 8, 3))), IF(AND([1]@row > 100, [2]@row < 100, [2]@row > 10), (VALUE(MID([Original IP]@row, 8, 3))), IF(AND([2]@row > 10, [1]@row > 10, [1]@row < 100, [2]@row < 100), (VALUE(MID([Original IP]@row, 7, 3))), IF(AND([2]@row > 10, [1]@row < 10), (VALUE(MID([Original IP]@row, 6, 3))), IF(AND([2]@row < 10, [1]@row > 10), (VALUE(MID([Original IP]@row, 6, 3))), IF(AND([2]@row < 10, [1]@row < 10), (VALUE(MID([Original IP]@row, 5, 3))), ""))))))))) maybe you find some possibility need to becovered in this formula but you can coverit with same way i did by usin if function with (And ,Or).

5- Column 4 Formula you can prepare it in the same concept as i did for column 3 to cover al possibility ( it need time )

6- Column 11 Formula =IF([1]@row < 10, "00" + LEFT([1]@row, 1), IF(AND([1]@row > 10, [1]@row < 100), "0" + LEFT([1]@row, 2), IF([1]@row >= 100, [1]@row, "")))

7- Column 22 Formula =IF([2]1 < 10, "00" + LEFT([2]1, 1), IF(AND([2]1 > 10, [2]1 < 100), "0" + LEFT([2]1, 2), IF([2]1 >= 100, [2]1, "")))

and you can create the formula for column 33 & 44 using te same concept.

Brad Shapiro

Hello Bassam, Thanks for the extensive solution. While it certainly solved the problem it's incredibly complex. In Excel it's done within the cell itself and is simple to implement (see formula below). I was hoping to be able to duplicate the in-cell solution with smartsheet but I guess it's not possible.

=TEXT(LEFT(A2,FIND(".",A2,1)-1),"000") & "." & TEXT(MID(A2,FIND(".",A2,1)+1,FIND(".",A2,FIND(".",A2,1)+1)-FIND(".",A2,1)-1),"000") & "." & TEXT(MID(A2,FIND(".",A2,FIND(".",A2,1)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2,1)+1)+1)-FIND(".",A2,FIND(".",A2,1)+1)-1),"000") & "." & TEXT(RIGHT(A2,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2,1)+1)+1)),"000")

Bassam Khalil

Hi @Brad Shapiro

Am Glad that you accept my answer, for your information, not all excel function is working in smartsheet and the developer team is making every effort to develop the system to cover all customer requirements, if you have any update you like it to be added please use the following form.

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.6326517.636058538.1603609427-1260905219.1603416350

Darrell Mathis

Thanks for the input. I would like to also have more options for data such as IP address. Example: identify asset location based on IP. Scenario - Asset list that shows all devices including IP address - another sheet that contains IP scheme (shows IP ranges for sites/locations). Using cross-reference I would be able to capture where the asset is located based on the IP address.