As someone who had never encountered formulas in any capacity before beginning to use Tableau, I often find myself encountering a problem and thinking ‘I bet there is a really simple way of solving this’. But, without being told, sometimes it can be difficult to know exactly what formulas exist even if there may be one out there that perfectly and very simply solves the issue you are encountering.
One such moment happened a few days ago when, whilst being taught Tableau Prep, I was faced with a string that looking something like this in each row: W118-684. I knew I only wanted the first character from each row of data and I sat there thinking ‘there must be a simple way of taking just the first character and leaving the rest’. And there naturally was a way – namely a Left string calculation. So I thought I’d write this blog post to save people from pondering the same question.
I have created an example string field as seen here:
For the purposes of this exercise we are going to try to separate out each name into individual columns within Tableau.
First of all we are going to separate ‘Chris’ from the merged field. For this we will use a Left function.
Create a calculated field, and type Left. Within the brackets the Left function wants to know the target string, and then the number of characters it should count starting from the left before stopping. The word ‘Chris’ has 5 letters in it so we type 5, but the basic format is:
Left([the name of your target field],the number of characters you wish to include)
This then gives us this result:
And as you can see we now have a separate field populated only by the ‘Chris’ that we wanted.
Next up we are going to use a Right string calculation to separate out the word ‘Clare’ from our merged field.
The Right calculation operates much the same as the Left calculation, but simply counts from the right rather than from the left.
So once again we should create a calculated field, this time writing Right. The format is essentially the same as with the Left:
Right([target field],number of characters the calculation should include)
So once again we target the Merged Names field, and ‘Clare’ has 5 characters so we ask the calculation to count 5.
The result we get is this:
And so we have successfully separated the ‘Clare”s out into their own field.
Finally we will use a Mid function to pull ‘Ross’ out from the middle of the merged string.
The Mid string calculation is similar once again to the Left and Right calculations we have already used, but this time requires slightly more information to operate properly. The basic format of it is this:
Mid([target string],the character at which the calculation should start counting, how many characters it should count for)
As written above as well as needing to know a start point (tip: count the number of characters from the left as that is what the calculation will do) the Mid calculation also needs to know how many characters it should continue before stopping. So for our example we want it to start on the ‘R’ (the 6th character – so we write 6), and then ‘Ross’ has 4 letters (so we write 4).
And so we get this result:
This is only the beginning of what you can do with string calculations, but hopefully it will help some of you to start to understand what they do, and maybe even get you out of a few jams.