How to Extract an Integer from a Text String in Excel
Excel and Google Sheets offer you several functions to extract a fragment from a text string. The other day we needed to take ID numbers from a list of URLs and sum up pageviews of links by ID. We exported the report from Google Analytics directly to Google Sheets and used the following function.
Suppose that cell A1 has the following text string:
First we replace every occurence of
/ with 100 spaces
REPT(" ", 100), which results in a really big string. Something like
https: www.weareevermore.com en news some-slug 23
Then we take the 100 righternmost with
RIGHT(string, 100), so this leaves us with the ID with some whitespace front of it
After that we remove this white space by using
Then just parse the given string to integer with
As a last step we just check if the result of this function is a number. If it is indeed a number, we display the result, if not, we display
Finally we have this beautiful function:
=IF(ISNUMBER(INT(TRIM(RIGHT(SUBSTITUTE(A1,”/”,REPT(“ “,100)),100)))),INT(TRIM(RIGHT(SUBSTITUTE(A1,”/”,REPT(“ “,100)),100))),”-”)
Depending on your situation you might need a different combination of functions, let us know in the comments section if you need help. Or would you solve this with another tool? Would you use a filter in Google Analytics? Let us know about that to!