Regular expressions in excel formulas

Question

TL;DNR: does excel formulas provide regex-like functionalities for pattern matching?

My task is to extract the numeric values from strings (containing addresses in this specific case). Solving the problem with VBA's regular expressions would be pretty straightforward, but unfortunately I would prefer not to use it.

Does excel formulas provide pattern matching functionalities with similar expressiveness? SEARCH seems to be pretty limited, and in order to work it needs to exploit regularities that, since the text is user-submitted, I can't really rely on.

Not really. The linked question uses _VBA_, this is about trying to use _formulas only_. — mercuk, Oct 15 '16 at 14:24
Since there are no formulas that allow using regex, it is an exact dupe. — Wiktor Stribiżew, Oct 15 '16 at 14:37
Here's an example without VBA: =VALUE(RIGHT(B2,LEN(B2)-FIND(" in ",B2)-3)) Where the cell B2 is: "1 in 139,838,160" And this extract the value: 139838160 — Christophe Guerrier, Jul 28 '17 at 06:47
Just a sidenote: Google Sheets supports [re](https://support.google.com/docs/answer/3098244)[g](https://support.google.com/docs/answer/3098292)[ex](https://support.google.com/docs/answer/3098245). — ADTC, Nov 16 '17 at 02:25

score 4 · Answer 1 · answered Oct 15 '16 at 14:16

4

There's no regex available in Excel formulas. Your best option is to create a function in VBA and then call it from Excel

Link explaining how to do it.

answered Oct 15 '16 at 14:16

Kritz

7,099
12
43
73

1

Actually for very simple regexp you can use combination of RIGHT/FIND/LEN in your formula. see https://superuser.com/questions/821498/is-there-a-way-to-use-regex-or-comparable-solution-in-excel-formulas/821757 but it is quite tedious and not as clean and modulable as VBA. – Christophe Guerrier Jul 28 '17 at 06:45

Regular expressions in excel formulas

1 Answers1