Single formula

[markoloughlin]

Hi,

I would like to take the following entry in a cell xxxx.xxxx.xxxx and change it to xx:xx:xx:xx:xx:xx

For example Cell A1 contains 001C.234F.E293
I want Cell B1 to contain the same value but formatted this way 00:1C:23:4F:E2:93

I need to replace the full stops with ":" - but may not be necessary with the next requirement
I need every 2nd character to be preceeded with ":"

I can't seem to make a single formula that will do this in one go. I will have 1,000's of these to so hence the need for a formula.

Any help is greatly appreciated.

[ChemistB]

Not very elegant but try
=SUBSTITUTE(MID(A1,1,2)&":"&MID(A1,3,5)&":"&MID(A1,8,5)&":"&MID(A1,13,2),".",":")
Does that work for you?

[markoloughlin]

Hi,

that does the trick. I forgot to look at the subsitute function. Many thanks for the quick reply.

[contaminated]

Hi

Function InsertSymbol(TxtString As String, Symbol As String, _
                            AfterEvery As Integer) As String
    Dim j As Integer
    Dim NewString As String

    For j = 1 To Len(TxtString) Step AfterEvery
        NewString = NewString & Mid(TxtString, j, AfterEvery) & Symbol
    Next j

    InsertSymbol = Left(NewString, Len(NewString) - 1)

End Function

How to use:

1. Copy above code.
2. In Excel press Alt + F11 to enter the VBE.
3. Press Ctrl + R to show the Project Explorer.
4. Choose Insert -> Module.
5. Paste code into the right pane.
6. Press Alt + Q to close the VBE.
7. Save workbook before any other changes.

Then use this formula
=InsertSymbol(SUBSTITUTE(A1,".",""),":",2)