CODEWITHSUNDEEP
google-sheets
kenitech
kenitech

Reputation: 1163

How can I SUM alphanumeric values in Google Sheets?

It's not the best system but I've been using ImportXML to pull in YouTube view counts for my videos so I can keep track. I knew at some point YouTube would make a front end change that would break this. So now recently instead of just the number inside of the DIV I am referencing they are always showing the word "views". So here's what I'm using now

=IMPORTXML("https://www.youtube.com/watch?v=qXnr03IIPTI","//div[@class='watch-view-count']")

This will output "300,000 views" (or whatever the current view count is)

Before they added the word "views" in this DIV I was able to add up this column. So I added another column to strip out the text.

(where G7 is the cell containing the above value)

=SUBSTITUTE(G7, "views", "")

I thought for sure I would then be able to run a SUM on this column but alas the SUM is 0. I believe this is something Excel would be able to deal with.

Any ideas here?

Bonus points if you watch the video :)

Upvotes: 1

Views: 814

Answers (3)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

You can convert it to a value:

=VALUE(REGEXREPLACE(IMPORTXML("https://www.youtube.com/watch?v=qXnr03IIPTI","//div[@class='watch-view-count']")," views",""))

or 

=VALUE(REGEXREPLACE(IMPORTXML("https://www.youtube.com/watch?v=qXnr03IIPTI","//div[@class='watch-view-count']"),"[, views]",""))

if you really want the commas etc, wrap the final amount back into a text at the very end if you want such as =TEXT("FINAL SUM","#,#")

enter image description here

Upvotes: 1

Max Makhrov
Max Makhrov

Reputation: 18707

If the result is in G7, use formula:

=JOIN("",REGEXEXTRACT(G7,"([0-9]+),([0-9]+),?([0-9]+)?"))*1

it will convert string "300,000 views" into number. It'll handle bigger numbers, like: 1,368,142 views. This formula replaces commas because in some countries comma is used as sepatator integer and decimal part of number.

Upvotes: 1

Ed Nelson
Ed Nelson

Reputation: 10259

Try this:

=REGEXEXTRACT(IMPORTXML("https://www.youtube.com/watch?v=qXnr03IIPTI","//div[@class='watch-view-count']"), "[0-9]*\,[0-9]+[0-9]+")

Upvotes: 0

Related Questions