Multiplying a Number Found in an Alphanumeric String

[Randomerz]

I am working on a project that someone none-too-bright started.

The cell that I need to update looks like this:

Monkeys
$233
Dogs
$159

Yes, these four line items are in one cell. How can I increase eac
value by 2.5%?

NOTE: The number of spaces to each value in each cell is not consistent
One cell might start with "Monkeys" and the next might start wit
"Apes"

Please help

 

[Ron Rosenfeld]

I am working on a project that someone none-too-bright started.

The cell that I need to update looks like this:

Monkeys
$233
Dogs
$159

Yes, these four line items are in one cell. How can I increase each
value by 2.5%?

NOTE: The number of spaces to each value in each cell is not consistent.
One cell might start with "Monkeys" and the next might start with
"Apes"

Please help!

The most practical way, for the long-term, would be to redo the project so as to have a better layout, with the entries in separate cells. You can easily split those cells into four columns using the Data/Text-to-columns wizard with a delimiter of ASCII code 10. This is entered by selecting the "Other" option at Step 2 (Delimiters) of the wizard. Then, with the cursor in the adjacent box, hold down the <alt> key while typing, ON THE NUMERIC KEYPAD, the three digits 0 1 0 sequnetially. Then release the <alt> key. Then you can rearrange things in a way that makes more sense.

If for some reason you cannot convince the PTB that redoing things is the best way to go, I advise the following:

Although you can tease out the segments, do the multiplication, and then put it back together using either helper columns or long and complex formulas, it is simplest, to perform this operation with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MultNums(A1,B1)

in some cell, where A1 contains the string, and B1 contains the factor by which you want to multiply. Note that if you want to increase the value by a percentage, B1 should contain a value equal to 1 + that percentage. E.g: 1.025 or =1 + 2.5%

=================================
Option Explicit
Function MultNums(s As String, mult As Double) As String
Dim vss As Variant
Dim i As Long
vss = Split(s, Chr(10))
For i = 0 To UBound(vss)
If IsNumeric(vss(i)) Then _
vss(i) = Format(vss(i) * mult, "$#0.00")
Next i
MultNums = Join(vss, Chr(10))
End Function
=============================

 

[lhkittle]

I am working on a project that someone none-too-bright started.



The cell that I need to update looks like this:



Monkeys

$233

Dogs

$159



Yes, these four line items are in one cell. How can I increase each

value by 2.5%?



NOTE: The number of spaces to each value in each cell is not consistent.

One cell might start with "Monkeys" and the next might start with

"Apes"



Please help!

Hi Randomerz,

We may not be on the same wave-lenght, but for the $233 try:
=(MID(A3,FIND("$",A3)+1,3)*2.5)

And for the $159:
=MID(A3,FIND("$",A3,FIND("$",A3)+1),4)*2.5

Gives you the values "outside" the original cell of 582.5 and 397.5
respectively. (Adjust A3 to your cell.)

If you want Monkey and Dog and the new values in the same cell, then there's more formula work to do, which I believe can be done...???

HTH
Regards,
Howard

 

[lhkittle]

Hi Randomerz,



We may not be on the same wave-lenght, but for the $233 try:

=(MID(A3,FIND("$",A3)+1,3)*2.5)



And for the $159:

=MID(A3,FIND("$",A3,FIND("$",A3)+1),4)*2.5



Gives you the values "outside" the original cell of 582.5 and 397.5

respectively. (Adjust A3 to your cell.)



If you want Monkey and Dog and the new values in the same cell, then there's more formula work to do, which I believe can be done...???



HTH

Regards,

Howard

Woops, better change the *2.5 to *1.025.

Howard

 

[Randomerz]

;1607877']On Thu, 6 Dec 2012 22:52:06 +0000, Randomer

I am working on a project that someone none-too-bright started.

The cell that I need to update looks like this:

Monkeys
$233
Dogs
$159

Yes, these four line items are in one cell. How can I increase each
value by 2.5%?

NOTE: The number of spaces to each value in each cell is no consistent.
One cell might start with "Monkeys" and the next might start with
"Apes"

Please help!-

The most practical way, for the long-term, would be to redo the projec
so as to have a better layout, with the entries in separate cells. Yo
can easily split those cells into four columns using th
Data/Text-to-columns wizard with a delimiter of ASCII code 10. This i
entered by selecting the "Other" option at Step 2 (Delimiters) of th
wizard. Then, with the cursor in the adjacent box, hold down the <alt
key while typing, ON THE NUMERIC KEYPAD, the three digits 0 1
sequnetially. Then release the <alt> key. Then you can rearrang
things in a way that makes more sense.

If for some reason you cannot convince the PTB that redoing things i
the best way to go, I advise the following:

Although you can tease out the segments, do the multiplication, and the
put it back together using either helper columns or long and comple
formulas, it is simplest, to perform this operation with a User Define
Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visua
Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MultNums(A1,B1)

in some cell, where A1 contains the string, and B1 contains the facto
by which you want to multiply. Note that if you want to increase th
value by a percentage, B1 should contain a value equal to 1 + tha
percentage. E.g: 1.025 or =1 + 2.5%

=================================
Option Explicit
Function MultNums(s As String, mult As Double) As String
Dim vss As Variant
Dim i As Long
vss = Split(s, Chr(10))
For i = 0 To UBound(vss)
If IsNumeric(vss(i)) Then _
vss(i) = Format(vss(i) * mult, "$#0.00")
Next i
MultNums = Join(vss, Chr(10))
End Function
=============================

Ron Rosenfeld you are a genius. Okay, on looking closer at the file, i
appears that they did not alt+enter in each cell, they just put a
inconsistent number of spaces - from one cell to the next - betwee
"Monkeys $233 Dogs $159."

In addition to that, there are occasionally items that are all number
(again with inconsistent item lengths and spaces between) as in "1563
$593 344532 $152" AND also items that contain numbers and alph
characters as in "2705d413 $111 5564h1456 $112" (minus the alt+ente
issue, your code seemed to properly account for the alphanumeric item
but it multiplied the numeric only items by 2.5% and included a "$
sign).

The one constant throughout is that the "$" sign precedes every price.

Really, thank you for your help. Please work your voodoo magic one mor
time

 

[Ron Rosenfeld]

The one constant throughout is that the "$" sign precedes every price.

Perhaps the following will do what you require. It multiplies every value that both looks like a number and is also preceded by a "$", by mult, and replaces it in the original string.

=====================================
Option Explicit
Function MultDollars(s As String, mult As Double) As String
Dim re As Object, mc As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = "\$(\d+(?:\.\d+)?)"
End With

If re.test(s) = True Then
Set mc = re.Execute(s)
For i = mc.Count - 1 To 0 Step -1
s = Left(s, mc(i).firstindex + 1) & _
Format(mc(i) * mult, "#0.00") & _
Mid(s, mc(i).firstindex + 1 + mc(i).Length)
Next i
MultDollars = s
End If
End Function
=============================