GS has brought this to us :
Rick Rothstein expressed precisely :
Why so many lines of code? <g

Just for clarity and ease for the OP to understand (better
self-documentation), AND because this solution uses an array.

Sub ParseAmountsRick()
Dim Cell As Range
For Each Cell In Selection
Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), "@_@_@_@_@_@_@_@_@"), "_")
Next
End Sub

I also wondered when/if you'd join in!
Very nice. It's what I'd prefer over using the array approach.

However, using Cell.Text doesn't work when the cells are formatted 'General'
and the amounts are pasted in. *Typing* 107150.25 in A1 displays as 107150.3,
indicating Excel does some 'unsolicited' rounding. Typing 2765.11 and 7025.11
displays as typed. Programmatic entry displays to precision (ie: without
rounding).

Also, the thousands separator would not be present if the value was not text
to begin with. I guess it can go either way depending on how the values were
captured, but all 3 functions convert numeric values to text anyway.

So typing 107,150.25 in A1 displays as typed (no rounding occurs) BUT the
Formula Bar does not contain the comma. In this case, Excel formats the cell
to the display thousands separator but stores the value without it.

--
Since we format the value, I'd use Cell.Value...

Sub ParseAmountsRick()
Dim Cell As Range
For Each Cell In Selection
Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace( _
Cell.Value, ".", ""), "@_@_@_@_@_@_@_@_@"), "_")
Next
End Sub

Well.., all might NOT be as I stated. Seems the rounding disappeared
when the column width was AutoFit. Kind of makes the rounding issue
mute unless the data is dumped into fixed width cols.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc