How to split numbers from one cell to make it look like Mac address

Question

How to split the data from one cell to 8 rows in excel

For example: There is given long data in one cell: 65178492194051241284... The sorted values should look like:

65:17:84:92
40:51:24:12

user_1461862 · Answer

Excel offers multiple options to help you build your own formula

LEFT is used to extract characters from the LEFT of the string (also MID might be useful in your case) https://www.excel-easy.com/examples/substring.html

Combining LEFT/MID with CONCATENATE you should be able to produce the expected output

Something similar to

=CONCATENATE(LEFT(A1,2),':',MID(A1,2,2),':', MID(A1,4,2),.... )

user_5514747 · Answer

You have Excel365 then could try-

=TEXTJOIN(":",TRUE,MID(INDEX(MID($A$1,SEQUENCE(INT(LEN($A$1)/8)+1,,1,8),8),ROW(1:1)),{1,3,5,7},2))

user_4617121 · Answer

This will only work in Excel 365:

=MID(TEXTJOIN(":",TRUE,MID(A1,SEQUENCE(1,LEN(A1)/2,1,2),2)),SEQUENCE(LEN(A1)/8,, 0)*12+1,11)

Or if you want a more readable (and slightly faster running) version:

=LET( t,A1,
     L, LEN( t ),
     S, TEXTJOIN( ":", TRUE, MID( t, SEQUENCE( 1, L / 2, 1, 2 ), 2) ),
     MID( S, SEQUENCE( L/8,, 0 )*12 + 1, 11 ) )

user_9758194 · Answer

With Microsoft Excel 365 you can use:

Formula in C1:

=TEXT(MID(A1,SEQUENCE(LEN(A1)/8,,,8),8),"00\:00\:00\:00")

If you haven't always have an exact multiple of 8 characters you should nest FLOOR():

=TEXT(MID(A1,SEQUENCE(FLOOR(LEN(A1)/8,1),,,8),8),"00\:00\:00\:00")