Cleaning text data in Excel tutorial

This tutorial shows how to trim spaces from text data, correct space repetitions, or replace a text with another in Excel using the XLSTAT software.

Dataset for cleaning text data

The data set include two ordinal categorical variables representing the responses to a survey.

Our goal is to clean up the responses. We will use the Cleaning text XLSTAT tool to remove spaces before, after, or between words as well as to replace 99 with unknown and excellent with wonderful.

Setting up a text data cleaning in XLSTAT

• Open XLSTAT.

• Select the XLSTAT/ Text mining / Cleaning text data. The dialog box pops up.

• In the XLSTAT interface, select columns A and D in the Data field.

• In the Options tab Set the maximum number of spaces between words to be equal to 1.

• Activate Replace the characters and select the column named “Words” in the Replace field and the column named “New words” in the By field.

Results of a text data cleaning

A new sheet, named Trim spaces, is displayed containing the transformed text data. As expected, spaces are trimmed and the targeted words are replaced.