Count of Leading Spaces in a field

[vinay4444]

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

[LordNeilLord]

Hi @vinay4444,

In the formula tool you can use REGEX_CountMatches([Value], "\s") to count the whitespace

[MarqueeCrew · Accepted solution]

@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:

1. Remove leading spaces
2. Remove whatever follows the leading spaces from the field
3. Count the remaining characters (leading spaces)

Cheers,

Mark

[danrh]

Another option: Length([Value])-Length(TrimLeft([Value]))

[MarqueeCrew]

@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]

Example

STRSPN("3034408896x105", "0123456789") returns 10.  This is a useful test to make sure a string consists of a set of characters.

Cheers,

Mark

[danrh]

@MarqueeCrew - huh!  I hadn't used STRSPN before.  Thanks for the tip!