Thanks:  0
Likes:  0

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

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

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!

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

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)

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

=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

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

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!

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

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.

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

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.

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

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