Multiplying a Number Found in an Alphanumeric String
 

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!

Multiplying a Number Found in an Alphanumeric String
 

On Thu, 6 Dec 2012 22:52:06 +0000, Randomerz wrote:


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
=============================

Multiplying a Number Found in an Alphanumeric String
 

On Thursday, December 6, 2012 2:52:06 PM UTC-8, Randomerz wrote:
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!









--

Randomerz

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

Multiplying a Number Found in an Alphanumeric String
 

On Thursday, December 6, 2012 7:09:13 PM UTC-8, wrote:
On Thursday, December 6, 2012 2:52:06 PM UTC-8, Randomerz wrote:

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!



















--



Randomerz



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

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

In addition to that, there are occasionally items that are all numbers (again with inconsistent item lengths and spaces between) as in "15634 $593 344532 $152" AND also items that contain numbers and alpha characters as in "2705d413 $111 5564h1456 $112" (minus the alt+enter issue, your code seemed to properly account for the alphanumeric items 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 more time!

Multiplying a Number Found in an Alphanumeric String
 

On Fri, 7 Dec 2012 16:28:59 +0000, Randomerz wrote:

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
=============================

Multiplying a Number Found in an Alphanumeric String
 

This is not working.
for eg: Cell A1: 67x89x6inch

I want each and every number must be multiplied by 2.54 (to convert in cm)

Multiplying a Number Found in an Alphanumeric String
 

This is not working.
for eg: Cell A1: 67x89x6inch

I want each and every number must be multiplied by 2.54 (to convert in cm)

Multiplying a Number Found in an Alphanumeric String
 

This is not working.
for eg: Cell A1: 67x89x6inch

I want each and every number must be multiplied by 2.54 (to convert in cm)

Multiplying a Number Found in an Alphanumeric String
 

Hi,

Am Sun, 15 Nov 2015 07:53:36 -0800 (PST) schrieb :

for eg: Cell A1: 67x89x6inch

I want each and every number must be multiplied by 2.54 (to convert in cm)

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "InchToCm"
There is an UDF to convert inches to cm.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional