Excel TRIM function is use to remove un-necessary space or “NORMALIZE” all spacing from the given text. This TRIM function has only one argument is TEXT.
Syntex.
=TRIM(text)
TRIM function keep only keep single space between two words/letters and remove all leading and trailing spaces.
Limitation of TRIM function
· TRIM only removes the space with ASCII code character (32) from text.
· Many time texts may contain with non-breaking space which have ASCII code character (160) that may appear from web page and other document, those non-breaking space cannot remove by TRIM function.
To remove those type of non-breaking space we need to use SUBSTITUTE function with CHAR function in excel to remove those un-necessary space.
First it needs to replace by all non-breaking space to regular space in given text by using SUBSTITUTE and CHAR function
Syntax of SUBSTITUTE function
=SUBSTITUTE(text,old_text,new_text,[instance_num])
text = text which need to TRIM
old_text = CHAR(160)
new_text = CHAR(32)
Optional argument [instance_num] ,which need not to specify
Where CHAR function will represent character ASCII code 160(non-breaking space) and character ASCII code 32(regular space) in the selected text.
In output of SUBSTITUE function, replacement of all non-breaking space to regular space.
last we can use TRIM formula as mention below to get the final text without un-necessary space.
=TRIM(SUBSTITUTE(text,old_text,new_text))
