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.
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.
Leave a Comment