SharePoint: Trim Function

The trim function serves as an excellent helper tool to clean text. We all know that if we have five people inserting data into a list, we’ll have five ways to represent the same thing.

So let’s explore a bit and see how we can use it.

Usage

It follows a simple pattern.

Text to trim

Removes all spaces from the string, except the single spaces between the words.

If you’re a developer, the way that trims works here can be disarming. In other programming languages, trim (or equivalent) functions only trim the spaces before and after. Power Apps also work this way with its trim function, but I like how Microsoft implemented this.

Let’s see some examples, starting with the spaces before:

'    SPACES BEFORE'

Trim([textColumn])

will return 

'SPACES BEFORE'

Spaces after:

'SPACES AFTER    '

Trim([textColumn])

will return 

'SPACES AFTER'

Now let’s combine:

'    SPACES BEFORE AND AFTER    '

Trim([textColumn])

will return

'SPACES BEFORE AND AFTER'

Finally, the whole enchilada.

 '    SPACES BEFORE AND AFTER    AND    IN    THE MIDDLE    '

Trim([textColumn])

will return

'SPACES BEFORE AND AFTER AND IN THE MIDDLE'

Looks fantastic, right? It’s ideal to “clean” text that contains uneven spacing.

Although you may use the trim function on other types, like a number, boolean, and dates, it will only return the expected values without changes since there is no uneven spacing. Finally, in the case of Dates, the function will return a number and not the formatted date. Please consider this if you want to convert the date into text.

Limitations

There are no documented limitations for this, but if you know some please let me know by email or Twitter.

Recommendations:

“Hidden” Validation Columns

Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose the “message” columns that have helpful error messages or actions that they can take to solve the problem.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your code. If you don’t validate a column, it can be used in other calculations, and the error will cascade. You can have a mistake in one column, and the issue could be in whole different columns, so deal with cases where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested Trim functions in a formula, you should review it and make everything more straightforward.

Sources:

Microsoft’s Trim Function Reference

Introduction to SharePoint formulas and functions

Back to SharePoint’s list formula reference.How to Microsoft’s SharePoint List “Trim” Function? Here you can find tips and tricks on how to use it efficiently.

Photo by Kelly Sikkema on Unsplash