How do i insert characters at regular intervals into cells?

Posted by Eric on September 16, 2001 12:12 AM

I've got a list of characters that i need to convert from a form looking like 00b0dfba156c to 00-b0-df-ba-15-6c .

I have too many cells to modify all by hand.. is there any way to get excel to insert dashes every third character?

Thanks,
Eric

Posted by Aladin Akyurek on September 16, 2001 12:37 AM

Eric --

Try:

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

Aladin

===========

Posted by Robb on September 16, 2001 5:07 AM

Eric

If you want to convert the cells in place, you could use Aladin's formula in code:

Copy this into a module (amend the sheet name and range to suit) and then run it. It should do the trick

Sub insert()
Dim rep As String
For Each c In Worksheets("Sheet2").Range("A1:D3").Cells
rep = Left(c, 2) & "-" & Mid(c, 3, 2) & "-" & Mid(c, 5, 2) & "-" & Mid(c, 7, 2) & "-" & Mid(c, 9, 2) & "-" & Right(c, 2)
c.Value = rep
Next c
End Sub

Regards

Posted by Aladin Akyurek on September 16, 2001 5:33 AM

Robb: That would be appropriate. (NT)