Force That Text to Wrap in Tableau

 

When you’ve used Tableau for long enough, you will inevitably run into a situation where you want some text element to wrap but, whatever you do, you can’t get it to wrap. Let’s say, for example, you’ve created a bar chart showing Sales by Product and, instead of having the Product Name to the left of the bar, you’d like to place it on the bar itself, like this:

 

But that text is really long, so you’d like to do some sort of text wrapping. You change the width of the bar chart in hopes that the text will wrap…

 

But that doesn’t work. So, you figure it must be a setting somewhere. You click the Label card, then go to the Alignment settings, and turn the Wrap setting to “On”…

 

 

But that still doesn’t do it. Unfortunately, no matter what you do, you just can’t get that text to wrap.

 

I have to admit that this has always stumped me. Sometimes it seems to wrap and sometimes, it doesn’t. From what I’ve observed, text will wrap if 1) It’s a discrete pill on rows or columns or 2) It’s on the label/text card but there is no axis (i.e. No continuous pill on rows or columns.

 

Since the majority of charts we create in Tableau do have a continuous axis, we’re often unable to wrap the mark labels on our charts. I recently ran into this problem and, in that particular case, unwrapped text simply was not an option. So, I set out to find some type of solution. In this blog, I’ll share a few different options for dealing with this problem.

 

Brute Force Calcs

Fortunately, in my case, the length of my text was somewhat predictable, with strings that were generally between 10 and 30 characters in length. I needed to force those strings to wrap into 1-3 separate lines where each line had a maximum of 15 characters.

 

For example, I might have a string like the following (this example is taken from the Product Name field in the Sample Superstore data).

 

Acco Economy Flexible Poly Round Binder

 

Ideally, I’d split this string into 3 lines with a maximum 15 characters, breaking on spaces, like this:

 

Acco Economy

Flexible Poly

Round Binder

 

To do that, I wrote some brute force calculations. Note: Split Length is a parameter with the max number of characters.

 

Split Pos 1

// First position to split the string.

// Basically the space before the nth character (specified by parameter).

IF FINDNTH(LEFT([Product Name], [Split Length]), " ", -1) = 0 THEN

    // Could not split, so simply split the string mid-word.

    [Split Length]+1

ELSE

    FINDNTH(LEFT([Product Name], [Split Length]), " ", -1)

END

 

Line 1

// First part of the wrapped string.

IF LEN([Product Name])<=[Split Length] THEN

    [Product Name]

ELSEIF [Split Pos 1]=0 THEN

    [Product Name]

ELSE

    LEFT([Product Name], [Split Pos 1]-1)

END

 

This gets our first line. We then create a calc to get what’s left.

 

Remaining 1

// Remaining portion of the string after the first split

IF LEN([Product Name])<[Split Length] THEN

    ""

ELSE

    TRIM(MID([Product Name], [Split Pos 1]))

END

 

Then we repeat the process with calculated fields for the second line. These are basically just copies of the first three calcs operating on what’s left of the string.

 

Split Pos 2

// Second position to split the string.

// Basically the space before the nth character (specified by parameter).

IF FINDNTH(LEFT([Remaining 1], [Split Length]), " ", -1) = 0 THEN

    // Could not split, so simply split the string mid-word.

    [Split Length] +1

ELSE

    FINDNTH(LEFT([Remaining 1], [Split Length]), " ", -1)

END

 

Line 2

// Second part of the wrapped string.

IF LEN([Remaining 1])<=[Split Length] THEN

    [Remaining 1]

ELSEIF [Split Pos 2]=0 THEN

    [Remaining 1]

ELSE

    LEFT([Remaining 1], [Split Pos 2]-1)

END

 

Remaining 2

// Remaining portion of the string after the second split

IF LEN([Remaining 1])<[Split Length] THEN

    ""

ELSE

    TRIM(MID([Remaining 1], [Split Pos 2]))

END

 

Since I know I’ll only need up to 3 lines, I only need these two sets of calculations.

 

Finally, I concatenate all the strings together, adding carriage return/line feeds between each.

 

Wrapped

// Final wrapped version of the string.

// Insert CRLFs in between each.

[Line 1] +

IF TRIM([Line 2]) = "" THEN "" ELSE CHAR(13) + CHAR(10) END +

[Line 2] +

IF TRIM([Remaining 3]) = "" THEN "" ELSE CHAR(13) + CHAR(10) END +

[Remaining 3]

 

This technique works well, but it has flaws. It’s a lot of logic and calculated fields, but more importantly, it’s not flexible enough to handle any length of text. It worked fine here because there were certain constraints on the length of my text, but what if that were not the case? Ideally, we could find a method that is able to perform this logic in a recursive manner.

 

Use SQL

If our data is in a database, we could use SQL to perform this operation. ANSI Standard SQL does not include looping mechanisms, but most databases implement Persistent Stored Modules (PSM) which allows for the creation of full-featured programs using SQL. PSM allows us to perform loops, so we could write some code that loops indefinitely, parsing the string, using similar logic to the Tableau calculations shared above. In the example below, I’ve written a SQL Server User-Defined Function that will wrap a line of text.

 

 We can then call this function via custom SQL:

 

SELECT dbo.WrapText([Product Name], 15) FROM [Orders]

 

Note # 1: Looping in a UDF isn’t always the most efficient way to do things in SQL, so this code is likely to slow your queries. There are probably other ways to do this in SQL, especially depending on the flavor of database you’re using. I’m simply sharing this as one possible method for performing this task using SQL.

 

Note # 2: If User-Defined Functions aren’t an option in your database, then you could also do something similar in a stored procedure. And, if that’s not an option (Tableau only supports SPs for a small number of databases), then you could use similar code in “Initial SQL”.

 

Use TabPy

Another option is to use Python. The beauty of Python is that there are libraries that can handle the hard work for us automatically without us having to define any of the logic. Specifically, I’ll be using a library called textwrap.

 

Note: Before we can use TabPy in Tableau, we’ll need to set up a TabPy server and configure the analytics extension. For details on how, see this Information Lab blog by Alex Fridriksson.

 

I personally prefer to do this type of data work in the data prep phase, so I’m going to build this in Tableau Prep instead of Desktop. I’ll start by writing my code.

 

 

Note: For a good introductory tutorial on using TabPy in Tableau Prep, see this Interworks blog by Gheorghe Ghidiu.

 

Then we’ll create a Script step in our Prep flow and select this code, specifying the wrap_product function.

 

This will create a new Product Name Wrapped field in our output that we can easily use in Desktop.

 

Note: We could also do something similar using R and Rserve.

 

Flaws

While all of the above options work well, I think they all have flaws. We discussed the flaws in the brute force method, but SQL and Python are also flawed. The looping mechanisms built using SQL are going to lead to less performant queries and it’s simply not an option for non-database data sources or databases with more rudimentary SQL implementations. And, while I love Python, using TabPy is simply not an option for many organizations as it’s just another piece of infrastructure that must be set up and maintained.

 

That being said, it sure would be nice if we could find a method we can implement directly in Tableau. I’ll admit that I almost gave up on finding such a solution, but then I remembered Regular Expressions!

 

Use Regex

As I’ve said previously, I’m terrible with Regex. There’s just something about the all the codes and symbols that my brain simply cannot fully grasp. Fortunately, the internet has tons of resources for using them. After a lot of searching and testing, I finally found a solution on Stack Overflow. The solution, shared using Javascript, is as follows:

 

s.replace(/\S{30}|[\s\S]{1,30}(?!\S)/g, '$&\n')

 

Now, I’ll be honest that I have no idea what all those symbols in the matching string mean!! But I tested it on regex101.com, using the text shared earlier, “Acco Economy Flexible Poly Round Binder”. Essentially, it will match strings up to 30 characters long, breaking them at the spaces—pretty much exactly what I needed. So, I translated it into Tableau:

 

REGEXP_REPLACE([Product Name], "(\S{30}|[\s\S]{1,30}(?!\S))", "$1" + CHAR(13) + CHAR(10))

 

Note: The last input to the function tells it to replace each match with that match plus carriage return and line feed characters (ASCII values 13 and 10).

 

We can then change this to use our Split Length parameter:

 

REGEXP_REPLACE([Product Name], "(\S{" + STR([Split Length]) + "}|[\s\S]{1," + STR([Split Length]) + "}(?!\S))", "$1" + CHAR(13) + CHAR(10))

 

Finally, I noticed that the regex keeps the space at the beginning of each line, so I trimmed it off with a REPLACE that will change any instances of carriage return line feed characters followed by a space to simply the carriage return line feed characters.

 

REPLACE(

REGEXP_REPLACE([Product Name], "(\S{" + STR([Split Length]) + "}|[\s\S]{1," + STR([Split Length]) + "}(?!\S))", "$1" + CHAR(13) + CHAR(10))

    , CHAR(13) + CHAR(10) + " ", CHAR(13) + CHAR(10)

)

 

And, with that one concise (although far from simple in my opinion) calculated field, we can wrap text however we like!

 

It’s a Wrap!

Oh boy, that is a terrible pun, but I couldn’t help myself!

 

We now have several methods for wrapping this text, but before we go, let’s apply it to our chart. We drop one of our new fields on the label card and viola, nicely wrapped text, exactly like we wanted!!

 

This is a pretty niche use case, but as I said in the introduction, you’re likely to run into this wall at some point. When you do, I hope one of these solutions will prove valuable in solving it. Thanks for reading!! If you have any questions or comments, please share them in the comments section below.

 

Ken Flerlage, May 20, 2024

Twitter | LinkedIn | GitHub | Tableau Public