Thursday, July 21, 2022

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

Image by digidesigner from Pixabay


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. 

 

 

Share:

Related Posts:

0 comments:

Post a Comment