This is a pretty basic little activity, but I’m documenting it here simply because it might, at some point, prove to be useful. In short, I wanted to find a way to measure how much engagement some instagram accounts we’re trying to force – that is, how many other people were they tagging as part of their instagram posts.
This is pretty straight forward – I had already scraped the content of the captions of the instagram posts for a variety of users. What I wanted to do, then , was find a way to interrogate those captions in order to determine whether there was a shout out or tag in there. Again, this was fortunate, because any tags in Instagram start off with the ampersand character – ‘@‘. So, what I needed to do was simply process every single caption from a user’s posts, and count the number of times @ appeared in each one. Then I could apply this rule to the whole CSV of collected posts.
To make this work, I put the @ character in a cell next to the caption, and then in the next cell, put this formula:
=LEN(B3)-LEN(SUBSTITUTE(B3,C3,""))
What this formula does is this. The substitute formula removes all of the @ symbols from the caption. For the purpose of this activity, lets say there are 29 characters in the original caption, and one ampersand. This means, after the substitute runs, there will be 29 in the original caption, and 28 in the second one. The LEN function subtracts the new caption from the original (29-28), which leaves us with 1 – the number of ampersands.
Done.