Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ============================= |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
|
|||
|
|||
Quote:
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ============================= |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
random alphanumeric string | Excel Worksheet Functions | |||
Extracting 7 digit number from alphanumeric string | Excel Discussion (Misc queries) | |||
Comparison of alphanumeric string | Excel Discussion (Misc queries) | |||
Multiplying to string arrays | Excel Discussion (Misc queries) | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) |