How to Normalize Text in Excel

Excel offers some efficient ways to normalize text. As the name suggests, text normalization refers to simplifying the text. To perform text normalization, Excel offers functions like TRIM, LOWER, and SUBSTITUTE. In this tutorial, you will learn how to normalize text in Excel.

How to Perform Text Normalization in Excel

Text Normalization refers to changing text into a particular canonical form. Before processing a text, it eliminates concerns of different versions of the same text. It makes sure the text is in a consistent form.

As Text Normalization involves in several texts operation, multiple functions need to be used. It includes the TRIM function to trim whitespace, LOWER function to change the case of text to lower, and SUBSTITUTE function to strip punctuation.

A formula for the LOWER Function

=LOWER(text)

Parameters for the LOWER Function

The parameter of the LOWER function:

•        Text (required) – the text for which you want to lower the case.

A formula for the  SUBSTITUTE Function

=SUBSTITUTE(text,oldText,newText,[instance])

Parameters for the SUBSTITUTE function

The parameter of the SUBSTITUTE function:

• Text (required) – The text for which you want to change.
• oldText (required) – The text to replace.
• newText (required) – The text to replace with.
• Instance (optional) – Instance of the oldText to replace with newText.

A formula for the TRIM function

=TRIM (text)

Parameters for the TRIM function

The parameter of TRIM function:

•        Text (required) – the text for which you want to lower the case.

Example

The following example contains sample text in A3: A5. To normalize it:

• Go to cell A3.
• Apply the formula,=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(A3,".", " "),","," "))) to A3.
• Press Enter to apply the formula to A3.
• Drag the formula from cells A3 to A5 using the fill handle on the bottom right.
  

  Figure 1: Text Normalization using SUBSTITUTE, TRIM, and LOWER function

The formula above uses multiple SUBSTITUTE function in nested form. By doing this, the function substitutes the series of punctuation characters like the full stop, hyphens, commas, semicolons, colons one by one. Once the outermost SUBSTITUTE the characters, then it moves to the second outermost function and so on.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.