inserting colon

this is the data
0014f8c49563

how to insert colon automaticaly in every 2nd character

output is >>> 00:14:f8:c4:95:63

Something like this will do it:

Function InsertCharsInString(strString As String, _
strInsert As String, _
lInterval As Long) As String

Dim lStart As Long
Dim strNew As String

lStart = -1

strNew = strString

Do While lStart < Len(strNew) - (lInterval + 1)
lStart = lStart + lInterval + 1
strNew = Left$(strNew, lStart) & _
strInsert & _
Mid$(strNew, lStart + 1)
Loop

InsertColons = strNew

End Function


Sub test()

MsgBox InsertColons("0014f8c49563", ":", 2)

End Sub


RBS


"xtrmhyper" <[email protected]> wrote
in message news:[email protected]...
>
> this is the data
> 0014f8c49563
>
> how to insert colon automaticaly in every 2nd character
>
> output is >>> 00:14:f8:c4:95:63
>
>
> --
> xtrmhyper
> ------------------------------------------------------------------------
> xtrmhyper's Profile:
> http://www.excelforum.com/member.php...o&userid=23851
> View this thread: http://www.excelforum.com/showthread...hreadid=556466
>

Test Sub should of course be:

Sub test()

MsgBox InsertCharsInString("0014f8c49563", ":", 2)

End Sub

RBS


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Something like this will do it:
>
> Function InsertCharsInString(strString As String, _
> strInsert As String, _
> lInterval As Long) As String
>
> Dim lStart As Long
> Dim strNew As String
>
> lStart = -1
>
> strNew = strString
>
> Do While lStart < Len(strNew) - (lInterval + 1)
> lStart = lStart + lInterval + 1
> strNew = Left$(strNew, lStart) & _
> strInsert & _
> Mid$(strNew, lStart + 1)
> Loop
>
> InsertColons = strNew
>
> End Function
>
>
> Sub test()
>
> MsgBox InsertColons("0014f8c49563", ":", 2)
>
> End Sub
>
>
> RBS
>
>
> "xtrmhyper" <[email protected]> wrote
> in message news:[email protected]...
>>
>> this is the data
>> 0014f8c49563
>>
>> how to insert colon automaticaly in every 2nd character
>>
>> output is >>> 00:14:f8:c4:95:63
>>
>>
>> --
>> xtrmhyper
>> ------------------------------------------------------------------------
>> xtrmhyper's Profile:
>> http://www.excelforum.com/member.php...o&userid=23851
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=556466
>>
>

i dont any idea for that command

any formula in excel?


tnx in advace

There is no command in Excel, that's why you need to write your own.

Make that code a Public Function in a module, then you can call it from the
worksheet with:
e.g. in cell A1, "=InsertCharsInString("0014f8c49563", ":", 2)"

Google for "Excel User defined function", you get a lot of help.

NickHK

"xtrmhyper" <[email protected]> wrote
in message news:[email protected]...
>
> i dont any idea for that command
>
> any formula in excel?
>
>
> tnx in advace
>
>
> --
> xtrmhyper
> ------------------------------------------------------------------------
> xtrmhyper's Profile:
http://www.excelforum.com/member.php...o&userid=23851
> View this thread: http://www.excelforum.com/showthread...hreadid=556466
>

tnx anyway?

how about concatenate?

If you know the string will be exactly 12 bytes long then you
can use the worksheet formula :

=MID(A1,1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&MID(A1,11,2)


NickHK wrote:
> There is no command in Excel, that's why you need to write your own.
>
> Make that code a Public Function in a module, then you can call it from the
> worksheet with:
> e.g. in cell A1, "=InsertCharsInString("0014f8c49563", ":", 2)"
>
> Google for "Excel User defined function", you get a lot of help.
>
> NickHK
>
> "xtrmhyper" <[email protected]> wrote
> in message news:[email protected]...
> >
> > i dont any idea for that command
> >
> > any formula in excel?
> >
> >
> > tnx in advace
> >
> >
> > --
> > xtrmhyper
> > ------------------------------------------------------------------------
> > xtrmhyper's Profile:
> http://www.excelforum.com/member.php...o&userid=23851
> > View this thread: http://www.excelforum.com/showthread...hreadid=556466
> >

Concatenate what ?

NickHK

"xtrmhyper" <[email protected]> wrote
in message news:[email protected]...
>
> tnx anyway?
>
> how about concatenate?
>
>
> --
> xtrmhyper
> ------------------------------------------------------------------------
> xtrmhyper's Profile:
http://www.excelforum.com/member.php...o&userid=23851
> View this thread: http://www.excelforum.com/showthread...hreadid=556466
>

tnx
Andrew Taylor

it works

GOD SPEED