Parsing Data in Excel: Pull from the Right Until You Hit a Delimiter

When you're parsing out data in Excel, you may want to pull data from the right of of a cell until you reach a space or other delimiter. So for example if you've got a lot of data in a cell which ends with a certain string, you may wish to only pull out that string which is preceded by a certain delimiter which appears more than once in the cell. In this example we want to pull out only the references to the National League (N) and the American Association (AA) in column C to column D, when there are multiple tilde delimiters in column C.

In order to accomplish this, we enter this formula in column D:

=MID(C331,FIND("=",SUBSTITUTE(C331,"~","=",LEN(C331)-LEN(SUBSTITUTE(C331,"~",""))))+1,256)

As we can see in this example, just the abbreviation for the baseball league gets pulled out to the adjacent column.

If you need to pull data from the left until the first occurence of specified string you can set the formula up this way.

=MID(A1,FIND("=",SUBSTITUTE(A1,"-","=",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))-1))+1,256) See the explanation of this formula at: http://www.ozgrid.com/Excel/last-word.htm

A Macro to Run Other Macros

This night's tip shows how to run the four macros mentioned back in February to simultaneously help format data on all worksheets of an Excel spreadsheet.

The macro to paste the values of all formulas on February 29, 2016.

The macro to unfilter data on February 25, 2016.

The macro to autofit colums and rows on February 20, 2016.

The macro to unfreeze on February 17, 2016.

You just need to insert this macro in a new module and edit it to refer to the macros you want to run. So:

Sub option1() macro1 macro2 macro3 End Sub Sub option2() Call macro1 Call macro2 Call macro3 End Sub

. . . becomes in this Night's example:

Sub option1() AutoFitAll UnFreeze Unfilter PasteValue End Sub

Sub option2() Call AutoFitAll Call UnFreeze Call Unfilter Call PasteValue End Sub

Follow the instructions provided on my YouTube channel.

https://youtu.be/R1qa1wjjIAs

Thanks to AJ for this tip at http://www.mrexcel.com/forum/excel-questions/11487-execute-multiple-macros.html

Excel Macro to Paste Values in All Cells in All Worksheets

Use this macro in Excel to convert all of the formulas on all worksheets of a spreadsheet to static values:

Sub Paste() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Worksheets If Sh.Visible = True Then Sh.Activate Sh.Cells.Copy Sh.Range("A1").PasteSpecial Paste:=xlValues Sh.Range("A1").Select End If Next Sh Application.CutCopyMode = False End Sub

Just insert it in a module in Visual Basic, and then go to View . . . Macros . . . View Macros and Run 'Paste'. This macro was posted by Andrew Poulsom to this site:

http://www.mrexcel.com/forum/excel-questions/63213-visual-basic-applications-code-copy-worksheets-paste-values.html