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

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" wrote
in message ...

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" wrote in message
...
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" wrote
in message ...

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:

--
xtrmhype
-----------------------------------------------------------------------
xtrmhyper's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=55646

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" wrote
in message ...

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

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)&":"&M ID(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" wrote
in message ...

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?


--
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" wrote
in message ...

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:

--
xtrmhype
-----------------------------------------------------------------------
xtrmhyper's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=55646