Count proceeding spaces

[hatstand]

Hi All,

I have file with a hierarchal structure that I pull in using Power Query. I would like to count the proceeding spaces before each text so each row will have a figure. I can count all of the spaces but not the proceeding ones. Does anyone know of a way to do this?

Thanks.

 

[jasonb75]

If you have trailing spaces or consecutive spaces mid text as well then this will not work

=LEN(text)-LEN(TRIM(text))

Single spaces between the first and last non-space characters in the text will be ignored.

 

[sandy666]

with white spaces
Text.Length(Text.TrimEnd([raw]))-Text.Length(Text.Trim([raw]))

 

[hatstand]

Thanks so much and some very handy formulas for my reference, really appreciated.

 

[sandy666]

if your text contain unwanted hard spaces you can try this
Text.Length(Text.Remove(Text.TrimEnd([raw]),Character.FromNumber(160)))-Text.Length(Text.Trim([raw]))
also you can use Text.Clean() for non-printable characters