How do I create a custom cell format in numbers that sections hex numbers?

I don't think it can be done with a cell format in the cell you are typing into. You can use a formula in an adjacent cell to put the delimiter between the sets of characters. The result will be text, not a number, so no doing math on it.

If you type your "numbers" into A2, you can use this formula in B2:

=TEXTJOIN(":",0,REGEX.EXTRACT(A2,"\w\w",0))

If your text does not have an even number of digits, the rightmost single digit will get dropped (because it does not match "/w/w"). Characters other than a-z, A-Z, 0-9 will also cause problems.

You may want to set column A (or whichever one you type into) as text format so what you type doesn't change after you hit enter (dropping leading zeros, for example).

> It would be much easier with regex but there's no support for it (that I know of.)

There is regular expression support, via the REGEX.EXTRACT() function.

Given a value "aabbcc" in cell B2, this function will return the format you request:

=REGEX.EXTRACT(B2,"(..).*",1,1)&":"&REGEX.EXTRACT(B2,"..(..).*",1,1)&":"&REGEX.EXTRACT(B2,".{4}(..)",1,1)

=> "aa:bb:cc"

If you're familiar with regular expressions you know there are a myriad of ways of parsing the text, so my approach may not be the best, but it should show you the idea.

Hi senseivita,

I don't understand how hex numbers work.

Perhaps you can try this:

Formula in B2 is LEFT(A2,2)

Formula in C2 is MID(A2,3,2)

Formula in D2 is RIGHT(A2,2)

Formula in E2 is B2&"-"&C2&"-"&D2 (& is a shorthand way to concatenate text).

Regards,

Ian.