Comma Delimiter Function

M

Mr Mc

Joined
Jun 13, 2006
Messages
2
Reaction score
0
Hello,

I have a string of numbers that are 16 characters in length in each cell, I would like to add a comma delimiter @ the 4th, 8, 12 and 15th characters. Please can somebody write a vba function, so that I have the comma-delimiter in place to use the text to columns separator.
Thankyou..
 
Comma delim

Mr Mc said:
Hello,

I have a string of numbers that are 16 characters in length in each cell, I would like to add a comma delimiter @ the 4th, 8, 12 and 15th characters. Please can somebody write a vba function, so that I have the comma-delimiter in place to use the text to columns separator.
Thankyou..
Click to expand...

Try this Mr Mc - As Excel has trouble with numbers longer than 15 format the original cell as Text. Then make sure you are in the cell to the right of the number you wish to break down.

Code as follows:

Sub Comma_Delim()
' Macro recorded 14/06/2006 by Zoddy
' This breaks down a 16 digit number and inserts
' a comma @ 4,8,12 & 15
'
' Make sure you nare in the cell one to the right
' that contains the number
num = ActiveCell.Offset(0, -1).Value
For numadd = 1 To 12 Step 4
newnum = Mid(num, numadd, 4)
resnum = resnum + newnum & ","
Next numadd
resnum = resnum + Mid(num, 13, 3) & "," + Right(num, 1)
ActiveCell.FormulaR1C1 = resnum
End Sub

Give it a try

Regards

Zoddy
 
Back
Top