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

suncandle

New Member
Joined
May 12, 2009
Messages
4
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!!!!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hello and welcome to MrExcel.

Try

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
=LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&RIGHT(A1,2)

Might be a better way than this but this should work
 

suncandle

New Member
Joined
May 12, 2009
Messages
4
Wow, those responses were super quick! Thank you so much everyone, and thanks for the welcome VoG.

I will give these a try and let you know how they work!
 

suncandle

New Member
Joined
May 12, 2009
Messages
4
That worked great - thank you again.

My next question - is there any way to have that formula work for the cell that the value is in? If there is an easy solution, I'd appreciate your sharing it.

:)
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try this: press ALT + F11 to open the Visual Basic Editor then Insert > Module then paste into the white space on the right

Code:
Sub test()
Dim c As Range
For Each c In Selection
    c.Value = Left(c, 2) & ":" & Mid(c, 3, 2) & ":" & Mid(c, 5, 2) & ":" & Mid(c, 7, 2) & ":" & Mid(c, 9, 2) & ":" & Right(c, 2)
Next c
End Sub
Press ALT + Q. Select the range of values to convert, Tools > Macro > Macros, click on test then click the Run button.

:eek: This is irreversible - there is no Undo. :eek:
 

Forum statistics

Threads
1,081,526
Messages
5,359,279
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top