Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Re: How do i insert characters at regular intervals into cells?

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

===========


OR Re: How do i insert characters at regular intervals into cells?

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


Robb: That would be appropriate. (NT)

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

--


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.