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.

I'm guessing you read right over the <g symbol, right?


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

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.

The reason I went with the Text property was that I was trying to cater to
the selected value being formatted as Text or as Numbers with 2-decimal
places. The problem with using the Value property with numbers is that loss
of trailing zeroes in numbers like 12345.00 or 12345.10. Anyway, I went back
to the drawing board and came up with this for the specific 9-cell
version...

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

and this for the generalized solution...

Sub ParseAmountsNewRickToo()
Dim Cell As Range
Const Size As Long = 11
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _
Format$(Cell.Value, "0.00"), ".", ""), ",", ""), _
Mid(Replace(String(Size, "@"), "@", "_@"), 2)), "_")
Next
End Sub

both version of which will handle the selected numbers being formatted as
Text or as Number with two decimal places.

Rick Rothstein (MVP - Excel)