From the course: Tableau: Mastering Calculations

Cropping and searching strings - Tableau Tutorial

From the course: Tableau: Mastering Calculations

Start my 1-month free trial Buy for my team

Cropping and searching strings

“

When working with strings, post-processing is often necessary or at least helpful. This can be a great challenge, especially with free-form text. It is easier when any post-processing we want to get done follows a specific pattern. For example, let's look at some phone numbers. U.S. phone numbers consist of a three-digit area code in parentheses, followed by a seven-digit local number separated by a dash for readability. Let's assume we only want the area code. Let's take a crack at a function that you may already know from Excel. Let's create a calculated field and call it phone area code. We are interested in the first five characters from the left, so the area code and the parentheses. So let's use the function LEFT. You can see that the function demands a string and the number of characters. The string in our case is phone and we are interested in the first five characters, so let's type in a 5. The calculation is valid, so let's confirm and close by clicking "OK". We drag this new field into the view and can see this extracted the area code. Conversely, we may just be interested in the local number. If you are sure that it's always the eight characters at the end of the string, we can use the reverse function, RIGHT. Let's create another calculated field called phone: local number, and let's type in RIGHT again for the phone number and the last eight characters. Alternatively, we could also use the function MID. MID returns the characters from the middle of a text string, given a starting position and a length. Let's again start by typing in the string value that we're interested in, still phone. Our starting position is position number seven, that is, five characters for the area code in brackets, the space, and then we start at position number seven. Now you can see that length is written in square bracket. This always means that this information is optional. If we really want to cut a part from the middle of a string, we can specify the length of this section here. However, we are interested in everything from character seven onwards to the end of the character string. So we can leave length out. This is particularly helpful if you are unsure of how long the local number is within the phone number. We can use the MID function to extract the area code in a better way. So let's try again in a new calculated field, phone: area code without parentheses. Let's again use the function MID for the phone number. This time we want to start at position number two. And we only want to extract the three characters of the area code. So after a comma, we add in the length three. So starting, after the opening parenthesis, we extract the upcoming three characters, which is exactly the area code. If you are unsure if there is, for example, an international prefix in front of the phone number like +1 for the U.S., then we can use the FIND function. FIND returns the position of a substring within a string. For example, we want to find the opening parenthesis. So let's type FIND for the phone number and the opening parenthesis. Now it's important to put this opening parenthesis in quotation marks. They can either be double as I used here or you can also use single quotation marks. We want to separate the phone number at the character after the bracket. So let's add in +1. Now we use this FIND function to replace the number two in the MID function that we have at the top. It is important that you make sure to always include all opening and closing parentheses that you need, especially when layering multiple functions. This can be particularly confusing here, as we also include a parenthesis in quotation marks that is not part of the function syntax. The FIND function can be particularly helpful for free-form text data that often do not follow a given pattern, or that often deviate from a given pattern. If the fixed pattern is strictly followed, then splitting strings may be easier with another function, and I will show you which function in the next video.

Contents