In today excel tip let us discuss on how to remove leading space and trailing space in a cell of Excel worksheet. By default there are three excel functions which are very useful in removing the space in a cell. The Excel function we are going to use cleanup the cell are CLEAN, TRIM and SUBSTITUTE function.
TRIM Excel Worksheet Function:
The TRIM function is used to remove spaces from a text excluding the one which is there in between two words. The syntax of TRIM function is as follows.
TRIM(text)
Examples:
TRIM(A17) which removes the spaces in the starting of the text and end of the text.
CLEAN Excel Worksheet Function:
The CLEAN worksheet function removes all the non printable characters from the text. In real time, we can use CLEAN function for data which is exported from the other applications which contains characters that may not print with your operating system. The syntax of CLEAN function is as follows.
=CLEAN(text)
If you want to remove non printable characters as well as space, then you need to first use CLEAN function and then use the TRIM function.
SUBSTITUTE Excel Worksheet Function
The substitute function helps you to replace old text with new text. The syntax for substitute function is as follows.
SUBSTITUTE(text,old_text,new_text,instance_num)
Text is the text or the reference to a cell containing text for which you want to substitute characters.
Old_text is the text you want to replace.
New_text is the text you want to replace old_text with.
Instance_num specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
As you could has assumed, now we can use this formula to replace blank with non blank. Look for the example below.
=SUBSTITUTE(A1,” “,””)
Leave a Reply