How to separate text in cell

[Oleg]

How to separate text in cell on 2 digit blocks using the separator ":"
Example:
Cell A1 --> 0040CA69402E
Cell A2 will be --> 00:40:CA:69:40:2E

[Cheeky Charlie]

I'm frustrated...

I've written two formulae which are not necessarily the best way of doing this - the point is that they are copyable - I'm sure I should be able to combine the logic into an array formula - instead of an ugly "left(x,2)" & ":" & (mid(x,3,2)) &...

Of course, this works...
=LEFT(A3,2)&":"&MID(A3,3,2)&":"&MID(A3,5,2)&":"&MID(A3,7,2)&":"&MID(A3,9,2)&":"&MID(A3,11,2)
but it ain't pretty

Any legends able to make the array formula I'm sure exists?

HTH

[NBVC]

Probably, there is a better way...but this for starters:

Please Login or Register  to view this content.

[NBVC]

Actually, if you install the free addin called Morefunc...(http://xcell05.free.fr/morefunc/english/) and use the MCONCAT() function, then you can use this array formula:

Please Login or Register  to view this content.

where A2 contains the code to parse.

edit: This formula also accomodates for any even-lengthed code

[MickG]

Hi Here's a UDF.
Right click sheet "Tab", Select "View Code", From Vb Window Select "Insert", "Module". Paste in new window.
To Run, Enter in cell:- =Colon(), (Select Cell with Data to go in Brackets)

Please Login or Register  to view this content.

Regards Mick