Hi i would like to check the number of leading spaces to determine the hierarchy , any idea how to get the leading spaces please?
@vinay4444,
If there can be no space other than in front of the characters, then @LordNeilLord's solution works. If spaces may occur elsewhere, I constructed a counting mechanism of:
Length(replace([field1],TrimLeft([Field1]),''))
Inside of this nested formula we do the following:
Cheers,
Mark
Hi @vinay4444,
In the formula tool you can use REGEX_CountMatches([Value], "\s") to count the whitespace
Another option: Length([Value])-Length(TrimLeft([Value]))
@danrh,
I agree that your formula works too. But did you know that it outperforms my formula? Testing on 10,000,000 records the average run-time of 8.5 seconds was reduced to 7.7 seconds. In comparison to a regular expression variation of the formula, both are significantly faster by almost 3x. The regular expression is the tortoise in that race.
But don't get too excited about beating me. I found this expression to be 6.8 seconds and so far the fastest of them all:
STRSPN([Field1]," ")
STRSPN(x, y)
Returns the length of the initial segment of the string [x] consisting of characters in the string [y]
STRSPN("3034408896x105", "0123456789") returns 10. This is a useful test to make sure a string consists of a set of characters.
@MarqueeCrew just used this formula 8 years later 😊thank you for posting this!
Assuming the only blank spaces are the leading ones you can do a Regex_CountMatches
You can also opt to parse the leading spaces out using the Regex tool then use the length formula on the output
