LOADING

Type to search

Design Patterns

Alteryx Design Pattern: How to Get the Value After the Last Delimiter

A common requirement is to get the post code (zip code for the US readers) from an address. When the address fields are concatenated into a single row of data the post code, while being at the end will be after a different number of delimiters. So if you need to extract this into its own field so it can then be used in software like Tableau or Power BI to plot locations on a map, this video and blog show you how to do that with a function called ReverseString.

ReverseString() is a function which can be used in the formula tool to reverse a string field so it is written backwards. Once you’ve done that, then it is possible to use a ‘Text to columns’ tool to know split on the first delimiter because you now want only the text at the front, albeit this is currently backwards. So once you’ve parsed the data you just use another reversestring formula to turn the zip code back around.

Tags:

1 Comments

  1. Travis Craven December 12, 2020

    Another option I like is Reverse(Substring(Reverse(String),0,Findstring(Reverse(String),’,’)). Comments are needed for someone that might review later but only one formula tool could be used. Would love to see more tricks using REGEX.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *