Spliting digits up to fit paper form

[GS]

Clif McIrvin used his keyboard to write :

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. Ergo>>edit>>run!

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

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>

 

[Revenue]

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.

 

[GS]

It happens that GS formulated :

Clif McIrvin used his keyboard to write :

I meant editing the value when you want to use the code for a new number of
digits. Ergo>>edit>>run!

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.


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

 

[GS]

GS has brought this to us :

Rick Rothstein expressed precisely :

Just for clarity and ease for the OP to understand (better
self-documentation), AND because this solution uses an array.


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.

 

[GS]

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

 

[Rick Rothstein]

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

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)

 

[GS]

It happens that Rick Rothstein formulated :

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

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.

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!

 

[GS]

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