Adding colon into existing cell

[djrischar]

I am inputting a large quantity of data into a excel sheet. When I scan the barcode on the device it put a string of 12 hexadecimal characters into a cell, however to be able to use them for my application I need string separated into groups of 2 separated by a colon. Is there a way to quickly add the colons to the cells?

EXAMPLE:
the data scans in as 1a2b3c4d5e6f but to use it I need it to look like 1a:2b:3c:4d:5e:6f

[ben_hensel]

Please Login or Register  to view this content.

Just dumb brute force, really.

[AlKey]

maybe with this

Please Login or Register  to view this content.

Data Range

	A	B
1	1a2b3c4d5e6f	1a:2b:3c:4d:5e:6f

[Glenn Kennedy]

Try this:

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&RIGHT(A1,2)

GK edit: Ooops. I made a coffee and forgot to refresh before posting!!!!

[ben_hensel]

@al

They're hex numbers, so the "evens" could be any of 0-f (0-9 & a-f) at random. I don't think SUBSTITUTE is the way to go.

[Jonmo1]

Just dumb brute force, really.

Nothing wrong with Brute Force, sometimes that's really the only choice.


Here's a VBA Custom UDF method.
Put this code in a new VBA module

Please Login or Register  to view this content.

Then use it in a formula like
=AddColon(A1)

[djrischar]

Thank you guys! I ended up using the brute force method since it didn't have to be anything fancy!

=MID(B2,1,2)&":"&MID(B2,3,2)&":"&MID(B2,5,2)&":"&MID(B2,7,2)&":"&MID(B2,9,2)&":"&MID(B2,11,2)