Count proceeding spaces

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
738
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,452
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,292
with white spaces
Text.Length(Text.TrimEnd([raw]))-Text.Length(Text.Trim([raw]))
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
738
Thanks so much and some very handy formulas for my reference, really appreciated.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,292
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
 

Forum statistics

Threads
1,085,755
Messages
5,385,696
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top