7

I have a spreadshet that requires me to do some matching,namely speaking its a list of bonds, their tickers(names) and some data about them. I want to match their tickers(names), I know how would regex to match them using regex ("[A-Z]+ \d{2,4}-\w+ \w+"), but I am not familiar with how to use them outside of VBA(yes I prefer to not use VBA, people that will use this dont' knwo VBA).

I read this piece by Microsoft, so I know that this question(statement in question not the answer) is not correct, but I never had any luck actually finding out how to implelemnt regex in Excel especially formulas(maybe in MATCH just maybe).

Lastly my an example( just the relevant column) to make my question more concrete, I want to return cell rows with tickers:

JPMCC 2012-1 A1 

Prepay %
Loss %
Credit Support
ETC...

MLMTI 2014-6 B4

Prepay %
Loss %
Credit Support
ETC...

Rows to return:

JPMCC 2012-1 A1 
MLMTI 2014-6 B4

Note that I have a lot of such bonds in this column, I need to find each one by one

Also If you find that using helper columns is usefull here I am open to that, for now I just want to avoid VBA.

If needed I'll provide further information.

Thank you

UPDATE

Data, in this case includes headers such as "Credit Support", "Cumulative Loss %", "Prepay %" etc. there are dozens of such headers but its all text text, numbers are found in different column.

  • I think we need to know a bit more about what data you're looking to match and how you're going to use it, and what the "data" cells will normally look like. Depending on those parameters, matching could be done to be as simple as "this line contains a stock ID (TRUE/FALSE)" or "this is the stock ID on this line". – Iszi Oct 6 '14 at 14:04
  • @Iszi I updated the question with data. – sgp667 Oct 6 '14 at 14:13
  • I don't really follow - I struggle to "read" a regex. Can you explain your regex design in words? – Mike Honey Oct 7 '14 at 0:02
  • There's a SO question that may address your question about regex and Excel – Sun Oct 7 '14 at 0:15
6

Excel does not have regular expressions, but you can certainly build up most of what they can do one formula at a time. Here are some examples:

Extract first space-delimited word:

B1: =FIND(" ", A1)
C1: =LEFT(A1, B1 - 1)

Check if the first word is all non-lowercase:

D1: =EXACT(UPPER(C1), C1)

Extract second space-delimited word:

E1: =FIND(" ", A1, B1 + 1)
F1: =MID(A1, B1 + 1, E1 - B1 - 1)

Extract 4-digit year if possible:

G1: =LEFT(F1, 4) - 0

You can create as many of these extractions and tests as you need to become convinced that the data in A1 matches the ticker format, and not the header format, and then use something like this to reproduce that cell only in the right cases:

H1: IFERROR(IF((B1+E1+LEN(C1)+LEN(F1)+G1+D1)*0=1,"",A1),"")

spreadsheet showing helper columns

3

The accepted answer is good. I will add that for RegEx specifically, you could pass a range to a VBA function and then evaluate its value using RegEx.

  1. Add a reference to "Microsoft VBScript Regular Expressions 5.5"

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

  1. Create a function that takes a range and then evaluates it using RegEx.

Call the function from an Excel formula -- the user doesn't have to know VBA, only the name of the function.

The output in Excel will update in real-time, so long as the function takes a range and not, say, a string. (Excel will know when the range has been modified, whereas if it's just a string passed from a cell, it won't know.)

Or you could also create a button or something that calls the VBA and does the filtering. That would satisfy the requirement that the user doesn't need to know VBA.

2

I would try Advanced Filters - you can set up a table of criteria using wildcards, and then point a Filter operation at it. You can find the Advanced button on the Data Ribbon, in the Sort & Filter section (in Excel 2013).

There's a very detailed walkthrough here:

Advanced Filters: Excel’s Amazing Alternative To Regex

0

One can also combine the approach provided in the accepted answer (i.e. FIND and extract with LEFT or RIGHT) with an array formula to mimic some regexp features without using VBA in some specific cases.

The original post mentions a regexp containing the bracket expression [A-Z], which can be represented by the array of strings {"A","B",...,"Z"}. So using the array form of FIND, we can test the input against a series of strings and get the results as an array.

For example, assuming we want to extract street names from addresses

Baker street 4 W
Bäckerstrasse 14
Rue des Boulangers 4a

a solution is to find any first white space followed by a digit, i.e. ' [0-9]' and cut out the string starting from that position. So with an input located in M14,

FIND(" "&{0;1;2;3;4;5;6;7;8;9};M14)

will return an array of position or #NA if not found. Adding an IFERROR around the FIND makes sure that the strings that are not found return 0

IFERROR(FIND(" "&{0;1;2;3;4;5;6;7;8;9};M14);0)

Assuming the strings that are being matched are mutually exlclusive we can SUM to get the position of ' [0-9]'. Finally shortening the string at given position, the final formula is

{=LEFT(M14;SUM(IFERROR(FIND(" "&{0;1;2;3;4;5;6;7;8;9};M14);0)))}
  • 1
    (1) This seems to be a lot more complicated than anything else on this page, including in the question itself.  Can you explain what it is doing?  (It might help if you explain your perception of the question — i.e., what question you are answering — as the original question is not very clear.) … … … Please do not respond in comments; edit your answer to make it clearer and more complete. … … (2) Using IF(FALSE; "blah blah blah"; …) makes your answer look more complicated than it is, and is (arguably) not a great convention. – Scott Mar 1 at 1:05
0

If there are two numbers you want to find or maybe a two pattern then use this formula to do the same.

=or(isnumber(find("1519",b3)), isnumber(find("1521",b3)))

In this example, "1519" and "1521" are the patterns, you can change that patterns also like,

=or(isnumber(find("foo",b3)), isnumber(find("bar",b3)))

You can add as many patterns you want, except excel has some limitations ;)

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.