Is there a way to use Regex( or comparable solution) in Excel formulas?

Question

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.

Answer 1

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),"")

Answer 2

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

2. 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.

Answer 3

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

Answer 4

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)))}

Answer 5

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 ;)