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.
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 ' 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.
“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.
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.