Your data analysis solution

Cleaning text data in Excel tutorial

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

Dataset

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the 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 by unknown.

Setting up a text data cleaning in XLSTAT

Select the Text Mining/Cleaning text data command in the XLSTAT menu:

The Cleaning text data dialog box appears:

In the General tab, select columns A and D in the Data field.  Set the maximum number of space between words to be equal to 1. Enter uknown to replace 99 values under the Replace field.

Click on the OK button to generate the output sheet.

Results of a text data cleaning

A new sheet, named Trim spaces, is displayed containing the clean text data (see below).

1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283