Text Toolkit by Ablebits

This is the next post in the Add-Ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at Text Toolkit by Ablebits. In summary, it enables us to easily make changes to text without writing formulas. What types of changes? Many! Examples include extracting text, changing case, trimming spaces, counting characters, splitting text, removing text, and swapping text.

Objective

Before we get too far, let’s get a general sense of what the add-in does. Basically, you can select a range of text and then apply a command that will transform the text … all without writing any formulas. For example, let’s say we have a list of state abbreviations, but they have mixed case like this:

We can simply select the range, and then open the add-in to view the various commands:

We select the Change Case command to view the options for that specific command:

We select UPPER CASE and bam:

Now that we can see the basics, let’s dig a bit further.

App Store

The Ablebits Text Toolkit add-in is available in the app store. To get to the app store, just click Insert > Get Add-ins. Install the Ablebits Text Toolkit add-in and you’ll be ready to go.

Once installed, you’ll see the Text Toolkit command icon in the Home tab.

Note: depending on your version of Excel, you may or may not have access to these add-ins.

In addition to using commands individually, we can use one after the other to perform multiple transformations to a range of cells. Let’s see how we can do this in the following example.

Example

Let’s say we copy updated price lists from a pdf or website or word document. When we paste into Excel, it may look like this:

We need to get the Item IDs (AB101, etc) into one column, and the price into another column. We can select the cells and use the Split Text add-in command. We can split by colon, as shown:

The add-in leaves the original column in tact, but adds these new columns:

We can then select the cells in column D and use the Extract Text command and select the Extract Numbers option:

The add-in leaves the original column in tact, and adds a new column that includes only the numbers. We can delete the columns we no longer need, and bam:

Note: there are of course many ways to accomplish any given task in Excel, so other options here include Flash Fill and helper formulas that use functions such as LEFT, MID, and FIND.

All Command Options

There are many commands available, and each command has multiple options. Here is a screenshot of the available commands:

And here are the options for each command.

Add Text

Extract Text

Convert Text

Change Case

Trim Spaces

Count Characters

Split Text

Remove Text

Remove by Position

Swap Text

Conclusion

This add-in helps users perform many types of text transformations. If you work with text in Excel, this add-in may be helpful. If you use any other add-ins, please let me know by posting a comment below … thanks!

Sample File