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:

https://www.weareevermore.eu/en/news/some-slug/23

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

23

After that we remove this white space by using TRIM(string)

23

Then just parse the given string to integer with INT(string)

23

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 -

IF(
 ISNUMBER(thefunction),
 thefunction,
 ‘-’
)

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!