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: -
TRIM: Trim function is used to remove additional leading and trailing spaces from the text in a cell.
Syntax of “TRIM” function: =TRIM (text)
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:-
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.