Excel Function: TRIM

Purpose

The TRIM function operates on a text value by replacing sequences of 2 or more spaces with a single space and removing leading and trailing spaces.

Its output is always a value of type text.

Invocations of the TRIM function take the form:

TRIM(text)

The TRIM function only recognises the ASCII space character (unicode character 32).
- It does not recognise the unicode non-breaking space character (unicode character 160).

Argument	Argument type	Description
text	Mandatory	A text value whose trailing & leading spaces will be trimmed.

Function(s)	Example	Purpose

Error value	Condition type	Condition
#VALUE!	Bad argument	A value of type currency, geography, or stock was supplied to the *text* argument.

Example	Formula	Result	Description
1	`=TRIM(" a b c d ")`	`a b c d`	Single spaces between characters are preserved, leading & trailing spaces are trimmed.
2	`=TRIM(" a b c d ")`	`a b c d`	Sequences of 2 or more spaces between characters are replaced with a single space.
3	`=TRIM(DATE(2023,5,7))`	`45053`	The date May 7, 2023 is stored as the number 45053. It’s converted to text and returned.
4	`=TRIM(TIME(12,0,0))`	`0.5`	The time 12:00:00 is stored as 0.5. It’s converted to text and returned.
5	`=TRIM(1)`	`1`	Numbers are treated as text.

Example	Value supplied to “text” argument	Result	Description
1	`MICROSOFT CORPORATION (XNAS:MSFT)`	`#VALUE!`	Supplying a stock value to the TRIM function causes an error.
2	`USD/EUR`	`#VALUE!`	Supplying a currency value to the TRIM function causes an error.
3	`Melbourne`	`#VALUE!`	Supplying a geography value to the TRIM function causes an error.

These errors can be mitigated by converting values of type stock, currency and geography to text before passing them to the TRIM function. I.e.:

TRIM(VALUETOTEXT(text,0))