Split String with Delimiters, From the Right, in Google Sheets

Background 

I needed to split a bunch of strings. I only needed some parts at the end of the string. So, I thought that I needed a function that can split a string with delimiters, from the end of the string. I have limited skills related to Google Sheets, I couldn't find a function to do this simple task. I might have missed it.

Use Case

Let's say we have a bunch of addresses, and we only need to get the city, state, and county. The addresses are clean. Length are not consistent, with the first bit of them may contain street names, numbers, buildings, and other details, which are not very consistent. However, the last bit are consistent, containing city, state and country. And in this case we are only interested with the last bit. 

Possibilities 

Google Sheets offers SPLIT formula which split strings with delimiters into several cells/columns. But,  since the addresses don't have consistent number of commas as the delimiters, we will end up with varying amount of columns used after the strings are split. And the last bit which contains city, state and country may fall into different columns. 

We may be able to use a combination of formulas, to get the last chunks consistently. Which, I'm not aware of. 

My Solution

I decided to make a custom function, which we can call in Google Sheets just like a formula. To start, at the time I did it, it was on the menu under Extesions - > Apps Script. Please refer to Google Sheets documentation regarding Apps Script.  

This is one of the most simple function. Here's what I came up with:

function split_reverse (str, delimiter) {

   return str.split(delimiters).reverse()

} 

This will split a string with delimiters and then place the split strings in each row. However, I'd like to arrange them to fill columns. I may have to use array functions, added to the above function to fix it. But, it isn't a priority now.

So, here is what I wrote in the cell as formula, with cell C2 as my input string. 

= TRANSPOSE(split_reverse(C2,", ")) 

Now, I got the last bit of the string, nice and consistently split from the end. 

 

 

Me and Randomness

Sometimes we see things that seem to be random or coincidental. And it seems to be okay to assume so. 

But, having deal with and analyzed lager data, I began to see less and less randomness. Things began to show patterns or one thing that leads to another. 

So, here, I am trying to say that when you see things that seem to be random, it's probably that:

A. You haven't identified the pattern yet, or

B. Maybe, you don't have enough data yet

If you can collect and analyze more data, perhaps, it isn't as random as you think. 

Cheers, 

Bie

Welcome!

I just setup this site and haven't put together anything yet. Meanwhile, please use this form to contact me. Please make sure that you get to the end of the form and hit submit. Otherwise, I won't receive your message.