10

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.

Improve this question
5
  • You may write a custom function using regex. Commented Oct 15, 2016 at 14:16
  • Not really. The linked question uses VBA, this is about trying to use formulas only. Commented Oct 15, 2016 at 14:24
  • Since there are no formulas that allow using regex, it is an exact dupe. Commented Oct 15, 2016 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 Commented Jul 28, 2017 at 6:47
  • Just a sidenote: Google Sheets supports regex. Commented Nov 16, 2017 at 2:25

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Actually for very simple regexp you can use combination of RIGHT/FIND/LEN in your formula. see superuser.com/questions/821498/… but it is quite tedious and not as clean and modulable as VBA.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.