I have a column in a Google Sheets spreadsheet containing text and numbers, and also URL's, and I need to sum up all the numbers in another column. Since this is the format the client wants to use, it is not an option to split the information in more than one column. So, the information in a cell (say, A1) is like:
Facebook: 1500 followers
https://facebook.com/user/AccountName/
Twitter: 2000 followers
https://twitter.com/AccountName/
Now, in B1 I have the formula from here
=SUM(SPLIT(AE4,CONCATENATE(SPLIT(A1,".0123456789"))))
which is working well, unless if the URL has any number. For example the code above gives me 3500, which is correct. But if the URL has numbers, like https://linkedin.com/user/a4df65b1, in that case I would get 3570, that is 1500+2000+4+65+1. The formula is taking the numbers in the URL and adding them up as well.
I tried the following code also:
=SUM(SPLIT(REGEXREPLACE(A1, "[^\d\.]+", "|"), "|"))
even with modifications to include only the numbers between spaces (because in URLs there are no spaces, I want to add up only numbers between spaces) like the following:
=SUM(SPLIT(REGEXREPLACE(A1, "[^([:space:]\d[:space:])]", "|"), "|"))
Both are not working since they include numbers within URL's in the calculation too.
How can I filter URL's out or include only numbers that are between spaces?
Seems to me you could just use this:
=SUM(SPLIT(A1," ")
All real numbers in your strings seem they will have a space before and after them; and any grouping that isn't a real number will be ignored by SUM.