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

#### suncandle

##### New Member
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!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### VoG

##### Legend
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
=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
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
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
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.

This is irreversible - there is no Undo.

#### suncandle

##### New Member
That worked perfectly! Thank you so much!!!! Have a great day.

Replies
6
Views
65
Replies
0
Views
61
Replies
4
Views
356
Replies
6
Views
45
Replies
0
Views
117

1,171,308
Messages
5,874,936
Members
433,082
Latest member
wanders

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back