Formatting IP Addresses

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'

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/13/20 Answer ✓

    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/13/20

    Hi @Brad Shapiro 

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

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/13/20 Answer ✓

    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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
    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.


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!