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

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

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