Question

How could I reformat an ip field of variable octect lengths (ex.: 192.168.10.1) to something stuffed, forcing 3 digits (ex.: 192.168.'10.''1)?

I have a big table containing many informations, one of wich is an ip address, and filtering it through FormattedSearch. The problem is I would like to sort the resultant table by ip, and the original format (with variable length octects) doesn't allow me. If I could convert every one, adding a prefix to every octect (as sowed above), it would resolve my problem.

PeterThoeny gave me an answerd concearning a similar problem with a date field (FormattingDate), but I think the SpreadSheetPlugin is just not enough this time.

Thanks in advance!

Environment

-- DanielGermann - 19 Nov 2008

Answer

If you answer a question - or have a question you asked answered by someone - please remember to edit the page and set the status to answered. The status is in a drop-down list below the edit box.

I also think it would be a bit a stretch to use $SUBSTITUE() and other functions to make IP addresses sortable, but you never know. You could enhance the SpreadSheetPlugin with a $IPTOHEX() or $IPSORTABLE() function.

-- PeterThoeny - 02 Jan 2009

In the meantime, here's a work-around to zero-pad each entry in the dotted quad to three digits so the addresses can be sorted logically. without additional javascript.

The formula is a bit cumbersome, so I've shown it built up in steps. "Angle-brackets" (<>) were used here simply to make whitespace characters visible.

1. Get the value in column 1:
   • Set MYFORMULA = $T(R$ROW():C$COLUMN(-1))
2. Convert "dotted quad" form to CSV so it can be interpreted as a list:
   • Set MYFORMULA = $TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)
   • Sample output at this step: < 9,152,50,6 >
3. Trim it:
   • Set MYFORMULA = $TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma))
   • Sample output at this step: <9,152,50,6>
4. Prepend 'OO' to each list item, and then replace all but the last three characters with null.
   The SpreadsheetPlugin interprets some numbers pre-pended with zero as octal (i.e., 0[0-7]*), which messes with the calculation of LENGTH. As a work-around, temporarily used the letter 'O' instead:
   • Set MYFORMULA = $LISTMAP($REPLACE(OO$item,1,$EVAL($LENGTH($item)-1),),$TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)))
   • Sample output at this step: <OO9, 152, O50, OO6>
5. Join the list with dots, and change letter O to number 0.
   • Set MYFORMULA = $TRANSLATE($LISTJOIN(.,$LISTMAP($REPLACE(OO$item,1,$EVAL($LENGTH($item)-1),),$TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)))),O,0)

The last entry shows that this method can handle the case where only some of the data is already zero-padded.

You can then use a style tag to hide the redundant data, like so: <div style=display:none>IP</div>

P.S. Instead, you could finesse this with a client-side sort of the fully-rendered table. E.g., http://www.workingwith.me.uk/articles/scripting/standardista_table_sorting

-- SeanCMorgan - 10 Mar 2009

Wow!

-- PeterThoeny - 10 Mar 2009

You were right Peter, it was a stretch

-- SeanCMorgan - 11 Mar 2009