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

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

2 Comments

  1. Steven Jezard on July 22, 2021 at 5:08 am

    Thanks for the excellent review Jeff.

  2. Edgar Walker on July 22, 2021 at 5:32 am

    Good morning.

    Add-ins are a must if you use Excel very day. My favourite is Spreadsheet Assistant.
    Have a great day.

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.