this is the data
0014f8c49563
how to insert colon automaticaly in every 2nd character
output is >>> 00:14:f8:c4:95:63
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks