Extracting text around a string in Excel

If you need to extract text from an Excel cell which is X number of characters before and Y number of characters after a given search term, you can use the MID and MAX formulas with a nested SEARCH formula.

A formula in this format:

=MID(A2,MAX(SEARCH("stole a",A2)-35,1),70)

. . . can be set to return the number of characters before the search string equal to the value given in the MAX formula (in this example 35) and the number of characters from the start of the search string moving to the right equal to roughly half the value given for the MID formula (in this example 70).

. . . the SEARCH formula finds the position in the cell containing text where the first character of the search term appears.

The MAX formula then gets the value of the SEARCH result minus the number entered in the complete formula. In this it is case 35.

When the result from the MAX formula is fed into the MID formula, it is used to determine the position where the extraction should begin, and how many characters after that point it should include.

Using DAX formulas in Pivot Tables

When calculating data in an Excel pivot table, you're not limited to the listed options in the value field settings under the summarize value field by selection. There are lists of calculations including the sum, average, max, and minimum values for data in the pivot table. You can also input DAX, or Digital Analysis Expression, formulas in the table, which will return a table as a result. A pivot table doesn't include median values in the list of preset values, but you can enter a DAX formula which will add it to the table.

Follow these steps explained by Bill Jelen here.

1. When selecting the data range for the pivot table select the option to add the data to the Data Model.

2. With the data set up this way, you'll see that in the PivotTable Fields box, you have the option to 'Add Measure' when you right click on the Range.

3. Give the new measure a name, and then enter MEDIAN as the formula selecting the field you want to analyze.

4. Set the appropriate number format, and use the Check DAX Formula tool to confirm that the formula will function.

5. The MEDIAN DAX formula is now listed as a new field for the Pivot table can be added to the displayed values.

Note that in this data set, the zero values are not an error. This is just showing that in each of the selected years most players had no stolen bases - except for 1908, when a majority of players had at least one stolen base.

Setting a text qualifier when importing data into Excel

If you try to analyze a load file in Excel 365, you may have noticed that you don't get the option to set the text qualifier. When you imported a delimited text file, or .csv file, the previous versions of Excel would let you choose a specific text qualifier - a character that set off text fields between delimiters so that when the same delimiter, such as a comma, was used in within an imported field, an error would not result.

However, if you try to import a load file into Excel 365, by using 'From Text/CSV' or 'Get Data . . . From File' this option will be missing:

You can set a delimiter but not a text qualifier. If you want to import data into Excel with the old options, go to File . . . Options . . . and check off the option for 'From Text (Legacy)'.

Now when you go to Data . . . Get Data . . ., you'll see an option for Legacy Wizards:

. . . this will allow you to import data using the old wizard which gives you the option to set a text qualifier.

Sadly, Excel doesn't allow you to enter a custom text qualifier.