Excel Formulas to Normalize the Text ~ A Complete Tutorial!!

Sometimes, while you import some text string from other devices or locations, they may with some punctuations, extra spaces, or upper cases which you want to remove and adjust. Here this tutorial introduces a formula that helps you to normalize the text in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

If you want to normalize the text in Excel, you can use the below formula.

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,” “),”)”,” “),”-“,” “),”:”,” “),”;”,” “),”!”,” “),”,”,” “),”.”,” “)))

Syntax Explanations:

LOWER – The LOWER function returns a lower-case version of a given text string.

TRIM – This function removes the extra spaces from the text and there is no space at the start or end of the text string. Read more on the TRIM function.

*SUBSTITUTE * – In Excel, the SUBSTITUTE function replaces existing text with new text in a text string when you want to replace text based on its content, not position.

A1 – It represents the input value.

Comma symbol (,) – It is a separator that helps to separate a list of values.

Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

First, we will enter the input values in Column B.

Here we need to normalize the text.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Verdict:

In this chapter, we have described the formulas to normalize the text in Excel. Hope you like it. Mention your queries in the comment box below.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Ahead:

Excel Formulas to Extract the Substring ~ A Complete Guide!!

Excel Formulas to Get the Last Word of a Text String ~ Quick Method!!

Formulas for Extracting Substring Between Two Texts in Excel 365!!

Excel Formulas to Count Total Words in a Range ~ Useful Tips!!