Advance with Assist shares quick solutions to common challenges encountered by the InterWorks Assist on-demand team.

Question: I have some de-identified data where the date is buried in different starting points within a text string. In Tableau, is there a way to find the year of participation without asking for help from my data engineers? Here’s an example of the string I’m talking about. Any help is appreciated!

The good news here is the answer is yes. Tableau does have the ability to locate the year that this client is looking for. If the data has an identifying characteristic that you can exploit to locate your text, this method can work with some common string functions.

First, we will use the MID function to start grabbing the year where it starts within the String text field we have. Since the year starts at different locations on each record, we can’t simply input a starting point, but rather we have to locate the text that we are able to exploit for location. Upon closer examination of this data, the date within each record is input each time in the same order and is the only part of the text where a – is used. Due to this, we can create our calculation to grab the information we’re seeking:

The use of FIND gives us the ability to locate the dash, then use that position to give MID its starting location and length. We then get the year that we’re after.

Exploring string functions is a must if you have heavy text fields, as they can save you from lengthy data reworks, but just remember that a different structure may be better in the long run. So weigh your options, and keep exploring what’s best for your organization.