I was going to work this out with excel formulas like text, mid, etc,
but this is not going to be a simple approach even going that way.

Ok, here are two separate form problems I have.

Form 1 requires whole dollars in one column, and the cents in
another. So, basically I have to drop the correct total in the right
bucket if we are going to do it through programming.

ie 27,651.09 needs to be "27,651" in one column and "09" in the
one
right next to it. These numbers are formula driven totals calculated
elsewhere that must be transformed for this one form, as archaic as
the form happens to be.

Form 2 is worse, this would take a number like "27,651.09" and divide
it one digit at a time for the approximate box on the printed form.


ie 2|7|6|5|1|0|9|

I need a straightforward approach as possible, because I usually get
criticized when I get too fancy.

Ideas greatly appreciated.


Bruce

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

--
Garry

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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

"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 :-)

"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



-----------

another approach, using worksheet functions:

=MID(TEXT(A1,"0000000.00"),1,1)
=MID(TEXT(A1,"0000000.00"),2,1)
=MID(TEXT(A1,"0000000.00"),3,1)
=MID(TEXT(A1,"0000000.00"),4,1)
=MID(TEXT(A1,"0000000.00"),5,1)
=MID(TEXT(A1,"0000000.00"),6,1)
=MID(TEXT(A1,"0000000.00"),7,1)
=MID(TEXT(A1,"0000000.00"),9,1)
=MID(TEXT(A1,"0000000.00"),10,1)


--
Clif McIrvin

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

Clif McIrvin wrote on 8/24/2011 :
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,
You could eliminate the check for the decimal by stripping it out
before you loop...

strData = Replace(Format([A1], "0.00"), ".", "")

--
I was thinking to 'pad' the string to always be Len=9. The wks can be
CF'd to hide leading zeros in the first 7 cols. This way, the digits
can be loaded into an array and 'dumped' into the wks.

--
Garry

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

Revenue formulated on Wednesday :
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 one way to handle the 2nd part...

Assume amounts are in ColA, and listed contiguously (ie: NO blanks).

Select the amounts to be parsed and then run this macro:

Sub ParseAmount4()
Dim sTemp As String, sVal As String
Dim c As Variant, i As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
sVal = "" '//initialize
For i = 1 To Len(sTemp)
sVal = sVal & "," & Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, Len(sTemp)) = Split(Mid$(sVal, 2), ",")
Next 'c
End Sub

--
Garry

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

I forgot to mention that you should substitute the column offset for
the 1st column of the location for the output. That means...

If starting at ColE then the offset would be:
Columns("E").Column - Columns("A").Column

Also, as I mentioned to Clif, you can use CF to hide the leading zeros
for any/all of the 1st 7 digits.

--
Garry

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

"GS" wrote in message
...


Clif,
You could eliminate the check for the decimal by stripping it out
before you loop...

strData = Replace(Format([A1], "0.00"), ".", "")

--
I was thinking to 'pad' the string to always be Len=9. The wks can be
CF'd to hide leading zeros in the first 7 cols. This way, the digits
can be loaded into an array and 'dumped' into the wks.



Good use of replace.

When always padding to 9 digits ocurred to me I went with worksheet
formulas instead of VBA.

Using your array idea, you could still work right to left and leave
leading zeroes as empty variants; i.e., BLANK cells and CF wouldn't be
necessary (and skip the padding.)

--
Clif McIrvin

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

Clif McIrvin formulated the question :
"GS" wrote in message ...


Clif,
You could eliminate the check for the decimal by stripping it out before
you loop...

strData = Replace(Format([A1], "0.00"), ".", "")

--
I was thinking to 'pad' the string to always be Len=9. The wks can be CF'd
to hide leading zeros in the first 7 cols. This way, the digits can be
loaded into an array and 'dumped' into the wks.



Good use of replace.

When always padding to 9 digits ocurred to me I went with worksheet formulas
instead of VBA.

Using your array idea, you could still work right to left and leave leading
zeroes as empty variants; i.e., BLANK cells and CF wouldn't be necessary (and
skip the padding.)

Clif,
In my reply code, I decided not to use an array since it was simpler to
'dump' one row at a time into the corresponding results cells.

--
Garry

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

Here's the array approach I mentioned. It does not enter leading zeros.

(Same assumptions apply as for previous post)

Sub ParseAmounts5()
Dim sTemp As String, vTemp(1 To 9) As Variant
Dim c As Variant, i As Integer, iLen As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
iLen = 1 '//initialize counter
For i = 1 To Len(sTemp)
If Mid$(sTemp, i, 1) 0 Then Exit For
iLen = iLen + 1
Next 'i
For i = iLen To UBound(vTemp)
vTemp(i) = Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, UBound(vTemp)) = vTemp
Erase vTemp
Next 'c
End Sub

--
Garry

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

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

Rick Rothstein (MVP - Excel)


Here 's the array approach I mentioned. It does not enter leading zeros.

(Same assumptions apply as for previous post)

Sub ParseAmounts5()
Dim sTemp As String, vTemp(1 To 9) As Variant
Dim c As Variant, i As Integer, iLen As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
iLen = 1 '//initialize counter
For i = 1 To Len(sTemp)
If Mid$(sTemp, i, 1) 0 Then Exit For
iLen = iLen + 1
Next 'i
For i = iLen To UBound(vTemp)
vTemp(i) = Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, UBound(vTemp)) = vTemp
Erase vTemp
Next 'c
End Sub

I was wondering if you were going to weigh in ... I like it!

"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

Rick Rothstein (MVP - Excel)


Here 's the array approach I mentioned. It does not enter leading
zeros.

(Same assumptions apply as for previous post)

Sub ParseAmounts5()
Dim sTemp As String, vTemp(1 To 9) As Variant
Dim c As Variant, i As Integer, iLen As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
iLen = 1 '//initialize counter
For i = 1 To Len(sTemp)
If Mid$(sTemp, i, 1) 0 Then Exit For
iLen = iLen + 1
Next 'i
For i = iLen To UBound(vTemp)
vTemp(i) = Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, UBound(vTemp)) = vTemp
Erase vTemp
Next 'c
End Sub



--
Clif McIrvin

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

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)

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

--
Garry

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

"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 :-)

Rick Rothstein submitted this idea :
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)...

Good idea!

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)


I'm not sure I'd go with the extra processing when the number of
amounts could be in the thousands!
Again...

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

--
Garry

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

Clif McIrvin laid this down on his screen :
"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

I can verify that storing a value in a defined name works as far back
as xl2000. Whether it works further back I can't say because I've never
developed for earlier versions. *However*, it would be harder to update
than changing a constant at runtime, *AND* the defined name isn't as
portable as is the code!<g

--
Garry

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

"GS" wrote in message
...


*However*, it would be harder to update than changing a constant at
runtime,

You just lost me .... changing a constant at runtime??? Don't you mean
compile time?

My thinking was with a worksheet defined value, there would be no need
to open the VBE to change the parameter.


*AND* the defined name isn't as
portable as is the code!<g

Very true. The choice of solution depends greatly on developer
preference and the specific characteristics of the work environment,
doesn't it?!


--
Clif McIrvin

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

Clif McIrvin used his keyboard to write :
"GS" wrote in message ...


*However*, it would be harder to update than changing a constant at
runtime,

You just lost me .... changing a constant at runtime??? Don't you mean
compile time?

I meant editing the value when you want to use the code for a new
number of digits. Ergoeditrun!

My thinking was with a worksheet defined value, there would be no need to
open the VBE to change the parameter.

If you mean use a named range on the wks then that's easier than
editing a defined name value, but still not portable with the code.


*AND* the defined name isn't as
portable as is the code!<g

Very true. The choice of solution depends greatly on developer preference
and the specific characteristics of the work environment, doesn't it?!

Basically!

I tend to make reusable code as friendly as possible, though, and so
I'd likely prompt myself for the number of digits to format to. In this
case I'd use Rick's idea of implementing the String() function for
constructing the format string.<g

--
Garry

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

Great comments everyone.... Thanks a lot...

I will have to study each of these carefully although I temporarily at
least worked out an approach with formulas like =mid(range1,range2,1),
and then going right to left, I subtracted 1 from the number in range2
to make it pick the previous digit. Range2 just contained a len
command of the text cell in range 1.

It happens that GS formulated :
Clif McIrvin used his keyboard to write :
"GS" wrote in message ...


*However*, it would be harder to update than changing a constant at
runtime,

You just lost me .... changing a constant at runtime??? Don't you mean
compile time?

I meant editing the value when you want to use the code for a new number of
digits. Ergoeditrun!

My thinking was with a worksheet defined value, there would be no need to
open the VBE to change the parameter.

If you mean use a named range on the wks then that's easier than editing a
defined name value, but still not portable with the code.


*AND* the defined name isn't as
portable as is the code!<g

Very true. The choice of solution depends greatly on developer preference
and the specific characteristics of the work environment, doesn't it?!

Basically!

I tend to make reusable code as friendly as possible, though, and so I'd
likely prompt myself for the number of digits to format to. In this case I'd
use Rick's idea of implementing the String() function for constructing the
format string.<g

Example...

Sub ParseAmountsRick3()
Dim Cell As Range, sFormat As String, Size As Long
Size = Application.InputBox("Enter the number of digits", Type:=1)
sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2)
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace( _
Cell.Value, ".", ""), sFormat), "_")
Next
End Sub

--
Garry

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

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

Amended example:
If user cancels InputBox...

Sub ParseAmountsRick3()
Dim Cell As Range, sFormat As String, Size As Long
Size = Application.InputBox("Enter the number of digits", Type:=1)
If Not Size = False Then
sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2)
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = _
Split(Format(Replace( Cell.Value, ".", ""), sFormat), "_")
Next
End If 'Not Size = False
End Sub

--
Garry

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

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)

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

GS expressed precisely :
Amended example:
If user cancels InputBox...

Sub ParseAmountsRick3()
Dim Cell As Range, sFormat As String, Size As Long
Size = Application.InputBox("Enter the number of digits", Type:=1)
If Not Size = False Then
sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2)
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = _
Split(Format(Replace( Cell.Value, ".", ""), sFormat), "_")
Next
End If 'Not Size = False
End Sub

Revised as per discussion with Rick to retain Clif's idea to format the
cell value to 2 decimal places so it works with text or numbers...

Sub ParseAmountsRick3()
Dim Cell As Range, sFormat As String, Size As Long
Size = Application.InputBox("Enter the number of digits", Type:=1)
If Not Size = False Then
sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2)
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = _
Split(Format(Replace(Format(Cell.Value, "0.00"), ".", ""),
sFormat), "_")
Next
End If 'Not Size = False
End Sub

--
Garry

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