cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Count of Leading Spaces in a field

Meteor

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
Alteryx Certified Partner

Hi @vinay4444,

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

Highlighted
Alteryx Certified Partner

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:

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

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar

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

Alteryx Certified Partner

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]

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar

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

Labels