inserting colon into a value at a specific interval

[non4prophet]

I've got a spreadsheet with a list of alphanumeric values:

62:EF:62:EF:9C4A
62:EF:62:EF:B09B
62:EF:62:EF:A0F4
62:EF:62:EF:AF71
62:EF:62:EF:B309

I'm trying to find a way to insert a colon between the final four
characters, so that they are separated into two pairs so that the
outcome is:

62:EF:62:EF:9C:4A
62:EF:62:EF:B0:9B
62:EF:62:EF:A0:F4
62:EF:62:EF:AF:71
62:EF:62:EF:B3:09

I've used file/folder renaming utilities before that allow you to
insert or replace a value by counting so many spaces from the
beginning or the end of a value, but I don't know how to do this in
Excel. I also use the freeware ASAP utilities frequently, but don't
see a way to do this. Any suggestions?

Thanks!

 

[Dave Peterson]

Are they always this format (and length)

If yes, then
=LEFT(A1,14)&":"&RIGHT(A1,2)

could work.

 

[Peter T]

Another one -

Sub Test()
Dim sFormat As String, sText As String
Dim cel As Range
Dim rng As Range

sFormat = ">@@@@@@@"

Set rng = Selection

For Each cel In rng
sText = Replace(cel, ":", "")
cel = Format(sText, Left(sFormat, Len(sText) * 1.5))
Next

End Sub

Sub TestTheTest()
Dim i&, s$
For i = 1 To 12
s = s & Chr(64 + i)
Cells(i, 1) = s
Next
Range("A1").CurrentRegion.Select

Test
' should be OK to run Test again over processed cells
' Test
End Sub

Regards,
Peter T

 

[non4prophet]

Another one -

Sub Test()
Dim sFormat As String, sText As String
Dim cel As Range
Dim rng As Range

sFormat = ">@@@@@@@"

Set rng = Selection

For Each cel In rng
sText = Replace(cel, ":", "")
cel = Format(sText, Left(sFormat, Len(sText) * 1.5))
Next

End Sub

Sub TestTheTest()
Dim i&, s$
For i = 1 To 12
s = s & Chr(64 + i)
Cells(i, 1) = s
Next
Range("A1").CurrentRegion.Select

Test
' should be OK to run Test again over processed cells
' Test
End Sub

Regards,
Peter T

Thanks guys!

I went with the 2nd solution provided by Peter T. I've never worked
with VB Scripts or Macros before, so I thought it might be a nice time
to try. This worked great! The only question I have now is that now
I have two titles for Macros, "Test" and "TestTheTest". I tried
deleting parts to remedy this with no good results. Like I said, it's
working, I was just curious if there was a way to consolidate the
script into one Macro. Sorry, I'm such a huge n00b at this.

Much Appreciated!

 

[Peter T]

Get rid of 'TestTheTest'. It populates some cells with different lengths of
text, selects those cells, then runs the macro 'Test'. Ie it tests the macro
named Test with the sample data it made.

Rename the macro 'Test' to something more meaningful.
Manually select the cells you want to process
Run 'Test' or whatever new name you gave it.

One way to run the macro is with Alt-F8

I'll try and remember not to post a macro named TestTheTest again !

Regards,
Peter T