"Rick Rothstein" wrote in message
...
Why so many lines of code? <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

If anyone is interested, here is my code generalized to allow the user
to set number of cells to fill via a Size constant (the Const
statement)...

Sub ParseAmountsRick()
Dim Cell As Range
Const Size As Long = 9
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), Left(Replace(String(Size, _
"@"), "@", "@_"), 2 * Size - 1)), "_")
Next
End Sub

Rick Rothstein (MVP - Excel)


To generalize further, use a defined name instead of a Const:

For instance, create a Defined Name such as: Defined_Name=9
(this works in xl2010, I don't know how far back you can assign values
to defined names.)

Sub ParseAmountsRick()
Dim Cell As Range
Dim Size As Long
Size = [Defined_Name]
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), Left(Replace(String(Size, _
"@"), "@", "@_"), 2 * Size - 1)), "_")
Next
End Sub


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)