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

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


0 views

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com