Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles CONDITIONAL FORMATTING How to Normalize Text in Excel

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.

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

Another blog reader asked this question today on Excelchat:
Solution examples
I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 3371-4
Solved by M. Y. in 19 mins
Hello, I need help with IF and SUBSTITUTE formula in excel
Solved by A. A. in 43 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc