Go Back

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


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


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.


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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need help to figure out how to normalize all my data to a value of 100
Solved by C. S. in 22 mins
I am looking to create a formula that concatenate text when data in two columns meet certain criteria for example Where the group ID is the same I want to concatenate the data from 'Text' columns that have the same criteria. Any advice will be gratefully received. Group ID Text Desired Result 1 Text 1 Text 1 1 Text 1 2 Text 2 Text 2 Text 2 2 Text 2 Text 2 2 Text 2 Text 2 Text 2 3 Text 3 Text 3 Text 3 Text 3 Text 3 Text
Solved by T. U. in 22 mins
question about time minus time text text text
Solved by O. J. in 26 mins

Leave a Comment