Hi i would like to check the number of leading spaces to determine the hierarchy , any idea how to get the leading spaces please?
Value | Leading Spaces |
ABC | 3 |
A1B1C1 | 6 |
A2B2C2 | 6 |
A21B21C21 | 9 |
A22B22C22 | 9 |
DEF | 3 |
D1E1F1 | 6 |
D11E11F11 | 9 |
Solved! Go to Solution.
Hi @vinay4444,
In the formula tool you can use REGEX_CountMatches([Value], "\s") to count the whitespace
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
Another option: Length([Value])-Length(TrimLeft([Value]))
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.
Cheers,
Mark
@MarqueeCrew - huh! I hadn't used STRSPN before. Thanks for the tip!