0

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.

0

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

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

enter image description here

  • Thank you, thank you, thank you Mate! This worked brilliantly! Sorry I hadn't responded earlier. Been on other projects. Thanks again! – Tom Putney May 23 '16 at 15:01
  • 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 May 23 '16 at 18:59
  • 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 May 24 '16 at 4:26
2

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 %
  • why do you start all of your formulas with +? – Máté Juhász May 17 '16 at 19:54
  • 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 Jun 17 '16 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.