Parsing a text string in Excel

I have the following strings of text in Excel cells and need to get the value with the "%" symbol. I have tried several approaches with FIND, MID, LEN, SUBSTITUTE, etc., but haven't found the solution yet.

99.3SN .7CU 1% LV-1000 ON TAPE             (I need "1%")         
96.5SN 3AG .5CU .5% LV1000 SPECIAL SHAPE   (I need ".5%")      
96.5SN 3AG .5CU .5% LV1000 SPECIAL SHAPE   (I need ".5%")
96.5SN 3.5AG .75-1% LV-1000 ON TRAY        (I need ".75-1%")
92.5PB 5SN 2.5AG .75% LV-1000              (I need ".75%")

Any help to point me in the right direction would be greatly appreciated.

Tom Putney

Posted 2016-05-17T17:33:17.137

Reputation: 3

Answers

You can achieve it with a simple formula after installing reg-ex add-in:

=RegExFind(A1,"[-.0-9]*%")

Máté Juhász

Posted 2016-05-17T17:33:17.137

Reputation: 16 807

Thank you, thank you, thank you Mate! This worked brilliantly! Sorry I hadn't responded earlier. Been on other projects. Thanks again! – Tom Putney – 2016-05-23T15:01:44.767

Would you be willing to share to VBA code behind the 'RegExFind' function? I am trying to grow in my understanding of how to build functions using regular expressions and would be grateful for the help. Any good resources for this that you can recommend would also be appreciated. Once again, thank you for your help. – Tom Putney – 2016-05-23T18:59:44.417

That add-in is not written by me, so even I don't have access to it. Also I don't really understand your question, if you want learn more about regular expressions that doesn't require you to see the code. Regular expressions are not commonly used in Excel, if you want to practice you may find more results related to notepad++ – Máté Juhász – 2016-05-24T04:26:00.830

There is either a "CU" or "AG" followed by a space and a %
What we want is the data after the space through the %

The following shows how to do this step by step with only one formula per cell, This could be combined into a single formula, but I think this is easier for someone to follow or modify

assumes the data is in A1,A2,...

in C1:

=+IFERROR(FIND("AG",A1),FIND("CU",A1))
this will get the location of the AG or CU

in D1:

=+FIND(" ",A1,1+C1)
this gets the location of the space by looking for a space starting past the previous found AG or CU.

in E1:

=+FIND("%",A1)
this finds the %

in F1:

=+MID(A1,D1+1,E1-D1)
this pulls out the data by using the location of the space and the location of the %

bvaughn

Posted 2016-05-17T17:33:17.137

Reputation: 733

why do you start all of your formulas with +? – Máté Juhász – 2016-05-17T19:54:49.513

I often will make an equation into a named equation. To do this, I copy and paste into the name manager. As a result I start with a + as a habit. Without the + it is harder to get the mouse correctly centered for what needs to be copied (=xyz is harder than =+xyz because it does not matter if I pick up the + or not, xyz and +xyz work, =xyz will not work as the name manager has a = to the left) – bvaughn – 2016-06-17T16:07:25.790