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”.
 
img1
 
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.

 
img2
 
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.

 
img3
 
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”.
 
img4
 
Follow below given steps:-

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

 
img5
 

  • 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.

 
img6
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube