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