count empty spaces

[Kbob]

I have a large spreadsheet that in column 1 has,for example, the following information

Total Color
Total Red
Red
Pink
Orange
Total Blue
Light Blue
Navy

I want to count the number of leading empty spaces in order to format the sheet.
For example;
Total Color has 0 spaces
Total Blue and Total Red have 1 space
The rest, 2 leading spaces

I would like to create a count of those leading spaces and then I will format each differently. Keep in mind that the colors have nothing to do with the formats, these are just an example of the lists I may have.

Thanks

 

[GlennUK]

When you say "I will format each differently" ... what exactly will you be doing?

 

[Kbob]

What I want to do is just add a column that counts the empty spaces
in each cell
so the calc would return
0
1
2
3
etc...

Thanks

 

[barry houdini]

Assuming your data in A1 down then in B1 copied down

=FIND(LEFT(TRIM(A1)),A1)-1

 

[GlennUK]

Well, Kbob, you've told me about counting the spaces again, but I asked about what "I will format each differently" means ... so what does it mean?

 

[Kbob]

Sorry GlennUK
what I will do is create a form where the user can hide the lines by choosing levels which would be determined by the number of spaces. So the sheet mgiht have 200 line items. I want the user to be able to choose either detail levels (the highest number of spaces) vs other summary levels (levels 1 and above.)
Thanks
KL

by the way the previous post from someone else did a trim, I don't want that)

 

[Kbob]

It looks like the formula from above does work! Thanks Barry!!

 

[Kbob]

So GlennUK, now that I have those numbers I want to create a macro that works like an autofilter. I have the levels calced by the formula above.
My macro will have a user form where the user will choose the level of reporting.
I would filter the reports so if they choose a level they can report on that level or a level smaller. I haven' t tried to create the macro yet, let me know if you have any ideas.
Thanks
Kbob

 

[GlennUK]

Wouldn't haven't an actual Grouping and Outlining for your table be better than operating through a macro? Do your total rows have formulae for totalling? ( if so, try generating an Outline automatically )

 

[Kbob]

I would be distributing this so I wanted to use macros that would reformat but allow users to change without knowing alot about excel. he workook would have a lot of sheets that would be formatted.