problem with access 2003 using instr to separate cells from comma delimited string.

[Robin & Sandra Ashley]

Help needed using instr to separate more then 1 comma separated numbers in
access 2003 query.

eg

Formulation Cell string : 1,1.456785,2,34.67844,3,476,4,5.56783,5,3.1245

result needs to be as follows in 10 separate cells

1 1.456785 2 34.67844 3 476.476 4 5.56783 5
3.1245


can separate 1st cell and 2nd cell ok but no good with others....hope you
can help

Robin

 

[Jim Cone]

In Excel, I would do it this way...
'--
Sub SplitThemUp()
Dim strNumbers As String
Dim vNumArr As Variant

strNumbers = "1,1.456785,2,34.67844,3,476,4,5.56783,5,3.1245"
vNumArr = Split(strNumbers, ",", -1, vbBinaryCompare)
Range("A1:J1").Value = vNumArr
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html




"Robin & Sandra Ashley" <[email protected]>
wrote in message
Help needed using instr to separate more then 1 comma separated numbers in
access 2003 query. eg

Formulation Cell string : 1,1.456785,2,34.67844,3,476,4,5.56783,5,3.1245

result needs to be as follows in 10 separate cells
1 1.456785 2 34.67844 3 476.476 4 5.56783 5 3.1245

can separate 1st cell and 2nd cell ok but no good with others....hope you
can help
Robin