It happens that Rick Rothstein formulated :
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?

No! I took it as meant!
I actually think I wrote more code than need be, but didn't have time
to trim it down AND wasn't looking to make it harder to understand not
knowing the OP's level of skill!<bg<g


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.

And so is why Clif suggested formatting the 'Value' to 2 decimal
places. I later think I should have stuck with that:

..Format(c.Value, "0.00")...

...because if the value was "12,345" (text) then Format("12,345","0.00"
results to 12345.00.

If the value was 12,345 (numeric) then Format(12,345,"0.00" results to
12345.00.

So.., text or numeric it still adds the decimal precision regardless if
the value is a whole number OR text, -AND- it removes any commas, no?

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( _
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( _
Format$(Cell.Value, "0.00"), ".", ""), _

Mid(Replace(String(Size, "@"), "@", "_@"), 2)), "_")
Next
End Sub

Why Size=11? (000,000,000.00)?

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

Agreed! However, I don't think we need to include the Replace for the
thousands separator. I tried this in the immediate Window and also on
the wks, and the Format function (as used) removes the comma[s].

Value as text:
Format("12345", "0.00") returns 12345.00
Format("12,345", "0.00") returns 12345.00
Format("12345.00", "0.00") returns 12345.00
Format("12345.10", "0.00") returns 12345.10

Same results for Value as numbers!

Rick Rothstein (MVP - Excel)

--
Garry

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