Monday 3 February 2020

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.



Answer



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


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


enter image description here


No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...