I'm using Google analytics, and the value for page path looks something like this:
We would like to pull out the number between '/p' and '.html'. Unfortunately, the random strings of stuff sometimes include a '/p'. Is there a way to always use the last instance of '/p', without knowing how many instances of it there will be?
-This table has 40 million+ rows and counting
-If a file does not contain the numbers in question, the calculated field should be blank
If there was a way to count how many times a character appeared ('/'), then one could simply use StrParts, as the numbers always appear immediately before the .html.
Unfortunately, the numbers are not limited to a certain number of characters. With the test data I am looking at, they contain values with 1-6 characters, or do not contain a value at all. The Right function is close, but not a guarantee, as '/pump/p1.html' is a possibility.
If there is a way to do recursion, then a combination of Substring and IndexOf would get the value
Another recursive option is to check if all values remaining are numbers, if that is possible
Is there another option I'm overlooking which could be useful in this scenario?
Thanks in advance for any assistance
Please sign in to leave a comment.