I have a somewhat related question, if you don't mind:

I have very large amount of text in a single cell, and I would like to extract multiple instances of text that appear between two specific words.

For example, here is the sample text in one cell:

{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}

This following formula does a good enough job of extracting the first headline:

=MID(C2,SEARCH("headline",C2)+2,SEARCH("source:",C2)-SEARCH("headline",C2)-4)

However it only extracts the first headline and nothing after it.

If possible, I would like to extract all of headlines within the text in that cell, and generate a vertical array of those headlines so that it looks like this:

GE Posts Profit

GE Dividends Shrink

GE Bankrupt

Is this possible?

Thanks very much.

The array formula that I entered in cell range B9:B11 is a user-defined function that I created. It extracts text from cell B3 based on a start and end string specified in cell C5 and C6 respectively.

Before using it you need to copy the VBA code below and paste it to a regular code module, instructions below.

Array formula in cell range B9:B11

=ExtractText(B3,C5,C6)

To enter the array formula select cell range B9:B11. Type the formula and then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

**ExtractText**(*text*, *start_word*, *end_word)*

text |
Required. A cell reference to the cell containing the text you want to extract. |

start_word |
Required. The first word you want to search for. |

end_word |
Required. The second word you want to search for. Text between the first and second word will be extracted, even if there are multiple instances. |

Function ExtractText(text As String, start_word As String, end_word As String) 'Dimension variables and declare data types Dim tmpArr() As Variant 'Count instances ccount = UBound(Split(text, start_word)) - 1 ReDim tmpArr(ccount) 'Iterate through text string For i = 0 To ccount 'Find start position of instance StartStr = InStr(text, start_word) + Len(start_word) 'Find end position of instance EndStr = InStr(text, end_word) 'Extract first instance tmpArr(i) = Mid(text, StartStr, EndStr - StartStr) 'Remove instance and save to variable again text = Mid(text, EndStr + Len(EndStr), Len(text)) Next i ExtractText = Application.Transpose(tmpArr) End Function

- Copy code above.
- Go to tab "Developer", click the "Visual Basic" button to open VB Editor.

- Click "Insert" on the menu.
- Click "Module" to insert a module to your workbook.
- Paste code to code module, see above image.
- Exit VB Editor and return to Excel.

Note, make sure you save your workbook with file extension *.xlsm (macro-enabled) in order to keep the code.

]]>The XNPV function calculates the net present value for cash flows that may or may not be periodic. Net present value is used in investment planning and capital budgeting.

Formula in cell C12:

=XNPV(C2, B5:B10, C5:C10)

XNPV(*rate, values, dates)*

rate |
Required. The discount rate. |

values |
Required. A number of values representing cash flow, the first value is optional and may be a cost or payment. Make sure the first value is a negative value if it is a cost or payment. At least one value must be negative and one value must be positive. |

dates |
Required. Dates that correspond to the cash flow values. |

The XNPV function returns:

- #VALUE! error if arguments
*are*non-numeric. - #NUM! error if any number in dates precedes the starting date.
- #NUM! error if values and dates don't have the same number of values.
- #VALUE! error if any number in dates is not a valid date.

The formula looks like this:

d_{i} = the ith, or last, payment date.

d_{1} = the 0th payment date.

P_{i} = the ith, or last, payment.

The RRI function calculates the growth of an investment in percent per period.

Formula in cell C6:

=RRI(C2, C3, C4)

RRI(*Nper, Pv, Fv)*

Nper |
Required. Nper is the number of periods. |

Pv |
Required. Pv is the present value. |

Fv |
Required. Fv is the future value. |

The YIELD function returns:

- #VALUE! error if arguments
*are*not a valid data type. - #NUM! error if the arguments are not valid.

The equivalent formula is:

=(C4/C3)^(1/C2)

or

=(Fv/Pv)^(1/Nper)

]]>The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.

For example, the DIM statement (among others) only declare a variable but it will be empty, nothing is referred until you assign an object using the SET statement.

What is an Excel object? It can be a chart, worksheet, a cell range or a shape among other things basically. Each object has usually a number of properties that you may change, for example, a worksheet may be hidden by changing the visible property to False.

The following macro is used in the above example.

'Name macro Sub Macro1() 'Declare variable Dim rng As Range 'Assign object Set rng = Worksheets("Sheet1").Range("B2:C9") 'Show object address MsgBox rng.Address End Sub

Set objectvar = {[ New ] objectexpression | Nothing }

Excel Name |
Description |

objectvar | The name of the object variable. |

New | Optional. Is used to create an object. |

objectexpression | A name of an object or another object variable with the same object type. |

Nothing | Deletes object. Recommended if an object holds a lot of memory that you don't need anymore. |

*How can I create a new worksheet and assign it to an object variable?
*The following macro creates a new worksheet and then shows the worksheet name using the assigned object.

Sub Macro2() Set obj = Sheets.Add MsgBox obj.Name End Sub]]>

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This kind of formula is often used to calculate commissions, bonuses, pricing, fees or charges, discounts, volume pricing, volume rebate, and performance incentives.

The table shows the different thresholds or levels that a specific percentage applies to, if an amount is larger than the first level then multiple calculations are necessary in order to calculate the total. The formula takes care of these additional calculations as well, no need for helper-columns.

Formula in cell C10:

=SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

There are four different values B10:B13 in order to demonstrate how the total changes based on the percentage and levels used, I will explain below how each value is calculated.

If $65 000 is used then the formula returns $2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925

Amount |
Percentage |
Result |

65 000 | 4.5% | 2 925 |

Total |
2 925 |

$125 000 returns $5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5000.

Amount |
Percentage |
Result |

100 000 | 4.5% | 4 500 |

25 000 | 2% | 500 |

Total | 5 000 |

$280 000 returns $7 860. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 30 000 * 1.2% = 360. 4 500 + 3 000 + 360 = 7 860

Amount |
Percentage |
Result |

100 000 | 4.5% | 4 500 |

150 000 | 2% | 3 000 |

30 000 | 1.2% | 360 |

Total | 7 860 |

$540 000 returns $10 580. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 250 000 * 1.2% = 3 000. 40 000 * 0.2% = 80. 4 500 + 3 000 + 3 000 + 80 = 10 580.

Amount |
Percentage |
Result |

100 000 | 4.5% | 4 500 |

150 000 | 2% | 3 000 |

250 000 | 1.2% | 3 000 |

40 000 | 0.2% | 80 |

Total | 10 580 |

The formula contains two SUMPRODUCT functions, the first one calculates the result based on the amount that is above a specific level and the corresponding percentage.

The second SUMPRODUCT function calculates tiered values based on the amounts up to the reached level and their corresponding percentages. The formula then adds those numbers and returns the total.

The first two logical expressions determine which tier the amount in cell B10 reaches.

(B10<$C$4:$C$7)* (B10>$B$4:$B$7)

becomes

(65000<{100000; 250000; 500000; 999999})*(65000>{0; 100000; 250000; 500000})

becomes

({TRUE; TRUE; TRUE; TRUE})*({TRUE; FALSE; FALSE; FALSE})

and returns {1; 0; 0; 0}.

The parentheses lets you determine the order of operation, we want to subtract before we multiply the arrays in order to get the correct result we are looking for.

(B10- $B$4:$B$7)

becomes

(65000 - {0; 100000; 250000; 500000})

and returns {65000; -35000; -185000; -435000}.

(B10<$C$4:$C$7)*(B10>$B$4:$B$7)*(B10- $B$4:$B$7)* $D$4:$D$7

becomes

{1; 0; 0; 0}*{65000; -35000; -185000; -435000}*{0.045;0.02;0.012;0.002}

and returns {2925; 0; 0; 0}.

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)

becomes

SUMPRODUCT({2925;0;0;0})

and returns 2925.

((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7

becomes

(65000>{100000;250000;500000;999999})*({100000;150000;250000;499999})*{0.045;0.02;0.012;0.002}

and returns {0;0;0;0}.

SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

SUMPRODUCT({0;0;0;0})

and returns 0.

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

2925 + 0

and returns 2925 in cell C10.

The formula can be simplified to:

=SUMPRODUCT($D$4:$D$7* ((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10-$B$4:$B$7)+ ((B10>$C$4:$C$7)* ($C$4:$C$7-$B$4:$B$7))))

but that formula is harder for me to explain.

]]>The ROUNDDOWN function calculates a number rounded down based on the number of digits to which you want to round the number.

Formula in cell D3:

=ROUNDDOWN(B3, C3)

ROUNDDOWN(*number*, *num_digits*)

number |
Required. The value to round. |

num_digits |
Required. A number representing the digit you want to round down to. |

The ROUNDDOWN function rounds

- down to the given decimal if num_digit is larger than 0 (zero).
- down to the nearest integer if num_digit is 0 (zero).
- down to the number to the left of the decimal point if num_digit is less than 0 (zero).

The ROUNDUP function calculates a number rounded up based on the number of digits to which you want to round the number.

Formula in cell D3:

=ROUNDUP(B3, C3)

ROUNDUP(*number*, *num_digits*)

number |
Required. The value to round. |

num_digits |
Required. A number representing the digit you want to round up to. |

The ROUNDUP function rounds

- up to the given decimal if num_digit is larger than 0 (zero).
- up to the nearest integer if num_digit is 0 (zero).
- up the number to the left of the decimal point if num_digit is less than 0 (zero).

The image above shows lines between each colored column, here is how to add them automatically to your chart.

- Select chart.

- Go to tab "Design" on the ribbon.
- Click "Add Chart Element" button.

- Click "Lines".
- Click "Series Lines".

Lines are now visible between the columns.

The easiest way to sum a cell range is to simply select the cell range and read the values in the status bar. It shows the total, the count of non-empty cells and the average.

In fact, you can customize the status bar to show even more data:

Here is how to show these calculations automatically in the status bar.

- Right click on the status bar with your mouse.
- Click "Numerical Count", "Minimum", and "Maximum", see image below.

The image below demonstrates these calculations enabled.

This is probably the most common task in Excel and luckily, there is an easy short cut to use.

- Select the cell range you want to sum.
- Press and hold Alt on your keyboard.
- Then press =

This will create a formula containing the SUM function and a cell reference to the selected cells, see image above.

You can also go to tab "Home" on the ribbon and click "AutoSum" button and get the same result. To create totals below all columns select cell range C13:N13 and press and hold Alt and then press =

The picture above shows a formula in cell C15 that sums a column in cell range C3:N12 based on the specified column header in cell C14.

Formula in cell C15:

=SUM(INDEX(C3:N12, 0, MATCH(C14, C2:N2, 0)))

The MATCH function returns a number representing the position of the given value in cell C14, in C2:N2.

MATCH(C14, C2:N2, 0)

becomes

MATCH("May", {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0)

and returns 5.

The INDEX function returns the entire column in cell C3:N12 if the row argument is 0 (zero).

INDEX(C3:N12, 0, MATCH(C14, C2:N2, 0))

becomes

INDEX(C3:N12, 0, 5)

and returns {61; 68; 13; 19; 69; 96; 5; 7; 14; 50}.

The SUM function adds the numbers given and returns a total.

SUM(INDEX(C3:N12, 0, MATCH(C14, C2:N2, 0)))

becomes

SUM({61; 68; 13; 19; 69; 96; 5; 7; 14; 50})

and returns 402.

Formula in cell C15:

=SUM(INDEX($C$3:$N$12,MATCH(C14,B3:B12,0),0))

This formula is very similar to the prior one, no explanation is needed.

This image displays the dataset converted to an Excel defined Table. When you click and drag to create cell references they are instantly changed to structured references, this means that you generally don't have to adjust the cell references when you add data to the Excel defined Table.

Formula in cell C15:

=SUM(INDEX(Table1,0,MATCH(C14,Table1[#Headers],0)))

]]>The image above shows you a formula in cell D3 that tries to get the smallest number from cell range B3:B12 but it returns an error. This happens if the cell range contains at least one error value.

Formula in cell D3:

=SMALL(B3:B12,1)

The SMALL function ignores text and boolean values but not error values, however, the AGGREGATE function lets you choose between a variety of functions (including SMALL function).

You also have the option to ignore error values, the second argument lets you specify this.

=AGGREGATE(15,6,B3:B12,1)

The AGGREGATE function contains these functions AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC.

You can see a list of available functions while entering the arguments in the function, see image below.

The second argument has the following settings, I chose 6 - Ignore error values.

The AGGREGATE function was introduced in Excel 2010, if you have an earlier Excel version then I recommend using the following array formula:

=SMALL(IFERROR(B3:B12,""),1)

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

]]>