From the course: Tableau 10: Mastering Calculations

Find and replace text in a string - Tableau Tutorial

From the course: Tableau 10: Mastering Calculations

Start my 1-month free trial

Find and replace text in a string

“

- [Narrator] Business data can change quickly. While sales values can be adjusted, you'll also find that some departments, products, and codes change as well. In this movie, I'll show you how to find and replace data within the string. I'll start in PowerPoint, and then move over to Tableau. I will mention three string functions here and show you how to use two of them over in Tableau. The first function I'll mention is FIND, which takes a string, and then the substring that you're looking for, and you can also start looking at a character after the first. In other words, you don't have to start with one. You could for example start with character number three. The next function is FINDNTH, which looks for a substring within a string, and it gives you the position of the Nth occurrence of a value. For example, the FIND function would tell you the number of character at which the first L appeared, or FINDNTH would tell you where the third L appeared within a string for example. I'll go into detail on FIND. I couldn't think of a good use for FINDNTH, but I think it's a really cool function, so I wanted to mention it. Finally, you have REPLACE, where you look for a substring within a string, and then replace it, and I will demonstrate how to use that. With that information as background, let's switch over to Tableau. In this Tableau workbook, which is the Find workbook that you can find in the Chapter08 Folder of the Exercise Files collection, I have a set of offer codes. These are the codes that you can type into a website or give over the phone when you make a hotel reservation for a discount. I have the offer code text, and then also the amount of the discount. I think we can all recognize the two that we want, 21% off and 23% off. I'll start by creating a Calculated Field that allows me to find the position of the letter B, within the offer code text, and it will be a capital B. I'll go to Analysis, and click Create Calculated Field, and I'll call it "FindTheLetterB," and then press Tab. The function again is FIND, and the string that I'm operating on is Offer Code Text. There we go. Then a comma, and the substring, which I'll put in quotes, is the capital letter "B," and I don't want to start past the first character, so I'll leave the Start argument blank. The calculation's valid, so I'll click OK. Now I can add the FindTheLetterB Field to my data area, because it's a measure, and it will display the position of the first letter B within each code. I'll drag that over, and I see that for the first couple, it doesn't exist, so I get a zero. Then for these three codes, it starts, or it occurs at the letter six, and then for these three it occurs at the letter four. Now let's say that I want to replace a value. Specifically, I want to replace the TR code within the offer code text with the letters CF. I'll drag the FindTheLetterB field back to Measures, and then go to Analysis, Create Calculated Field, and I'll say, "ReplaceWithCF," and press Tab. The function I want to use is REPLACE. The string is Offer Code Text, then a comma. Now I need to tell it the substring to look for, so I'll put it in quotes, and that will be "TR." Again, capital letters. Then a comma, and the replacement string, also in quotes is a capital "CF" followed by double quotes. The calculation's valid, so I will click OK. Now I have my ReplaceWithCF field here in the Dimensions area, because I don't have a number, and when I drag it to the data area and drag a little bit to the right to expand, I see that instead of these four codes having TR, they in fact have the letters CF. If you need to find a value, you can use the Find function, or FINDNTH if you want to find the Nth occurrence, and you can replace values using REPLACE.

Contents