Formula to add a ":" after every 2 characters in a cell?

[suncandle]

Hi Everyone,
I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!

 

[hyperslonic]

Hi, welcome to the forum!

You could remove some of the maths from the SEQUENCE and MID() functions if you wanted, like this.

=TEXTJOIN(":";1;MID(A1;SEQUENCE(LEN(A1);;;2);2))

Thanks! Yes, your formula is much better. But LEN(A1)/2 is still more correct.

 

[Peter_SSs]

Since we are looking at alternatives with newer Excel versions, & given the original specs that the strings had an even number of digits, with 365 this would now be an option.

25 02 10.xlsm
	A	B
1	00904BB303D6	00:90:4B:B3:03:D6
Every 2

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(":",,REGEXEXTRACT(A1,"..",1))