Removing Redundant Characters from a Range of Cells and Resetting the Format in Microsoft Excel 2010

In this article, we will learn how to remove the redundant characters from a range of cells and how to reset the format. We will use Text, Trim and Substitute function in Microsoft Excel 2010 to get the desired outcome.

SUBSTITUTE: This function is used to replace the existing text with new text in a text string.

Syntax of “SUBSTITUTE” function:=SUBSTITUTE(text,old_text,new_text,[instance_num])

Example: Cell A2 contains the text “By hook or by crook”

=SUBSTITUTE (A2, A2,"By hook or by crook")

The function will return “By hook or by cook”.
 

 
TEXT:-This function is used to convert a value to text in a specific number format.

Syntax of “TEXT” function:    =TEXT (Value, format_text)

For Example: -

• Cell B2 contains the date 12-Feb-2013
• Write the formula in cell C2 =TEXT(A2,"MMMM")
• Press Enter on your keyboard.
• The function will return the month name.

 

 
TRIM: Trim function is used to remove additional leading and trailing spaces from the text in a cell.

Syntax of “TRIM” function:           =TRIM (text)

Example:

• Cell A2 contains the text “Excel Tips”
• Enter the formula in cell B2 =TRIM(A2)
• Press Enter on your keyboard
• The function will return the text after deleting the unnecessary spaces.

 

 
Let’s take an example to understand how we can remove the redundant characters from a range of cells and reset the format.

The text values in the range A2:A5 contain redundant parentheses.

We want to create a formula that will remove the parentheses and set the format of the cells to “general”.
 

 
Follow below given steps:-

• Write the formula in cell B2.
• =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(A2,")",""),"(","")),"General")
• Press Enter on your keyboard.

• The function will remove the redundant character from a range of cells and reset the format.
• Copy the same formula and paste in range B3:B5 by pressing the key Ctrl+V on your keyboard.