I want to extract a string from a cell using a regex match. I can't find the excel function that does it and I'm no VBA expert either. I use Excel 2007.
相关问题
- Excel sunburst chart: Some labels missing
- Error handling only works once
- Error handling only works once
- Improve converting string to readable urls
- Excel formula in VBA code
相关文章
- Optimization techniques for backtracking regex imp
- Regex to check for new line
- Get column data by Column name and sheet name
- Allow only 2 decimal points entry to a textbox usi
- Comparing speed of non-matching regexp
- programmatically excel cells to be auto fit width
- Unregister a XLL in Excel (VBA)
- Unregister a XLL in Excel (VBA)
In the Excel VBA code editor select Tools/References. In the picker check the latest version of Microsoft VBScript Regular Expresions.
Here is a simple user defined function to get you started. This will return "foo" if you point it at a cell with "foobar" or "fooooobar" in it.
There's a library available in Excel's VBA system known as Microsoft VBScript Regular Expressions. Once you include the library from the internal VBA 'IDE', you can create a simple macro that accepts whatever value you'd like to parse and return the result back to the caller (which can be a function call from a cell).
Here's a function that will let you pass in a cell then any regex pattern. Use () around the part of the pattern you want the function to return.
@AndrewCowenhoven Has a good answer. It's simple to add the Regex Library. Just to expand this answer a little, I will add what I learned to get items within matches.
For example, I have a string like this in one cell.
doubleclick.net/activityi;u=5f2256de37ab4992927b3a0a0a34f983;u13=;u14=27.34;u16=AUD;u9=;u10=;u11=;u12=;u5=;u6=;u7=;u8=;u1=Car;u2=SEA;u3=SEA;u4=20130923%20%7C%2020130926;ord=5360319407191.128?
I only need a few sections to verify that the doubleclick tag sent to google is correct. Using The regex shown above I have something like this.
I know this could be simplified a lot, but the point is that I'm pulling the submatches using parans to get specifically what I need.