inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
inserting colon
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 |
All times are GMT +1. The time now is 06:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com