"Revenue" wrote in message
...
On Aug 23, 4:00 pm, GS wrote:
GS presented the following explanation :





Try...

Sub ParseAmount()
Dim vTemp As Variant
vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As
Variant
RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

Assumes ColA is where the amounts are listed, ColB and ColC is where
the parsed values go.

Assumes you want a 2-digit result for the cents in ColC, AND you will
format ColB to include thousands separator. Otherwise, the function is
not needed...

Sub ParseAmount2()
Dim vTemp As Variant
vTemp = Split(Range("A1").Value, ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

OR...
If you want the result to be formatted as text (ie: left aligned):

Sub ParseAmount3()
With Range("A1")
.Offset(, 1).Resize(1, 2) = Split(.Value, ".")
End With 'Range("A1")
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted
text -

- Show quoted text -

Ok, I liked the approach above best for spreading the number between
two columns.

Now, how do I get the 2nd part of my puzzle to work where 27651.09
becomes split one digit to a column?

Somehow when I get this looping and putting numbers in different
places, the routine needs to start in the right column. For example,
if the next row has 107150.25, then the 1 in this example must start
in the appropriate column so that the placeholders line up properly .
It would be best for it to count from the right most column; I am
allowed 9 columns in the table. These digits all represent dollars and
cents.

1 0 7 1 5 0 2 5
2 7 6 5 1 0 9
7 0 2 5 1 1


---------

Here's a bit of air code just showing one way to grab the individual
digits starting from the right. I leave it up to you haow to use it.

I use the format function to guarantee there will always be two decimal
places; if there happen to be more than two in the source data format
will round the result.

I'm using A1 as the source cell.

dim strData as string
dim strDigit as string
dm ii as integer

strData=format([A1],"0.00")

for ii = len(strData) to 1 step -1
strDigit=mid(strData,ii,1)
if strDigit < "." then ' ignore decimal point ... remove if ...
endif to include "."
your code here
endif
next ii


--
Clif McIrvin

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