3 Best way to remove spaces or special characters from text values from Excel cells

Removing spaces or special character from text values is very crucial task in excel reporting projects. Because, if text value contains spaces or white character or any special then it results into data mismatch for using functions like Vlookup, Index-Match, sumif and so on.

However, to minimize chances of data mismatch in various such functions which works on text input is to trim text values for spaces or special character which is not required in data. Excel is enriched with few functions which will help us to remove spaces or unwanted special character in text values.

See below ways which will help you to remove spaces and unwanted special character in text values.


1) Use "=TRIM(text)" Function to trim text values:

This is regular trim function in excel which is generally used to remove spaces in text values

2) Use "=TRIM(CLEAN(text))" Function to remove non-printing characters:

If you need to remove spaces and non-printing characters from text values then you can used this formula which is combination of trim and clean function.

=TRIM(CLEAN(A1))

CLEAN function removes non-printing characters from text (line breaks, etc.). Thus, combination of trim and clean will give text values by removing spaces and non-printing characters.

3) Use "=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),CHAR(32))))" Function to  remove all type of special characters:

Note that CLEAN function is not capable to remove all non-printing characters, notably a non-breaking space, which can be represented in Excel as CHAR(160). By using SUBSTITUTE function to the formula, you can resolve this problem be replacing character 160 with a regular space before TRIM and CLEAN.