Blog

Formula - How to trim/remove leading or trailing characters from strings

There's often a need to strip or to remove specified characters from the start or the end of a string. This post summarises a selection of functions to carry out this task.

Here's a selection of formulas to trim characters from the start and at the end of an input string.

1 - Strip/Remove final character from a string

A common requirement is to remove the final character from a string. A typical use case scenario is to strip trailing commas or semicolons. Here is the formula to carry out this task. 

//Remove the final comma:
//"Apples, Bananas, Oranges,"

With({input: "Apples, Bananas, Oranges,"},
     Left(input, Len(input) -1)
)

This formula calls the Left function to return the leftmost number of characters from the input string, based on the number of characters in the original string - 1.

One thing to note is that the formula will not behave as expected if the input string contains trailing spaces. To workaround this issue, we can call the TrimEnds function to remove trailing spaces at the start and end of the input string. 

//Remove the final comma, ignoring trailing spaces:
//"Apples, Bananas, Oranges, "

With({input: TrimEnds("Apples, Bananas, Oranges, ")},
     Left(input, Len(input) -1)
)

2 - Strip/Remove final X characters from a string

A variation of the above formula is to remove n characters from the end of an input string. The formula beneath demonstrates how to remove the rightmost 14 characters from an input string. 

//Remove the right most 14 characters

With({input: "2022-02-01T18:35:50.901Z"},
     Left(input, Len(input) - 14)
)

3 - Strip/Remove the first character from a string

Here is the formula to remove the first character from an input string. 

//Remove the first comma:
//", Apples, Bananas, Oranges,"

With({input: ", Apples, Bananas, Oranges,"},
     Right(input, Len(input) -1)
)

This formula calls the Right function to return the rightmost number of characters from the input string, based on the number of characters in the original string - 1.

Like the earlier example, this formula will not behave as expected if the input string contains preceding spaces. To workaround this issue, we can call the TrimEnds function to remove the spaces at the start and end of the input string.
. 
// Remove the first comma, ignoring preceding spaces:
// "  , Apples, Bananas, Oranges, "

With({input: TrimEnds(" , Apples, Bananas, Oranges, ")},
     Right(input, Len(input) -1)
)

4 - Strip/Remove X characters from the start of a string

To remove n characters from the start of an input string, we can use the formula beneath. This highlights how to remove the first 5 characters from the start of a string.

// Remove 5 characters from the start of the string:
// "Name:Tim"

With({input: "Name:Tim"},
     Right(input, Len(input) - 5)
)

5 - Remove a set of characters from the start of a string

There are several methods to remove a preceding set of characters from the start of a string. One way is to call the match function in conjunction with regular expression.

The formula beneath highlights another method. This highlights how to remove the characters "000" from the start of an input string, if those characters exist.

// Remove "000" from the start of the string "00065896":

With({
        input: "00065896",
        ltrim: "000"
     },
     If(StartsWith( input, ltrim),        
        Right(input, Len(input) - Len(ltrim)),
        input
     )   
)

6 - Remove a set of characters from the end of a string

For completeness, here's a formula to remove a set of characters at the end of an input string. This formula removes the characters "EUR" from the end of an input string, if those characters exist. 

// Remove "EUR" from the end of the string "52698 EUR":

With({
        input: "52698 EUR",
        rtrim: "EUR"
     },
     If(EndsWith(input, rtrim),        
        Left(input, Len(input) - Len(rtrim)),
        input
     )   
)

Conclusion

This post highlighted a selection of formulas to remove characters from the start or the end of an input string. The typical use for this type of formula is to remove leading or trailing characters. .
Previous
Next