Spliting digits up to fit paper form

[Revenue]

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

 

[GS]

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

 

[GS]

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

 

[Revenue]

GS presented the following explanation :








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

 

[Clif McIrvin]

GS presented the following explanation :








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)
endif
next ii

 

[Clif McIrvin]

GS presented the following explanation :








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)

 

[GS]

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)
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"), ".", "")

 

[GS]

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

 

[GS]

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.

 

[Clif McIrvin]

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

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

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.)

 

[GS]

Clif McIrvin formulated the question :

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

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

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.

 

[GS]

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

 

[Rick Rothstein]

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)

 

[Clif McIrvin]

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

 

[Rick Rothstein]

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)

 

[GS]

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

 

[Clif McIrvin]

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

 

[GS]

Rick Rothstein submitted this idea :

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

 

[GS]

Clif McIrvin laid this down on his screen :

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>

 

[Clif McIrvin]

*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?!