count empty spaces

Kbob

New Member
Joined
Feb 19, 2005
Messages
36
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
When you say "I will format each differently" ... what exactly will you be doing?
 

Kbob

New Member
Joined
Feb 19, 2005
Messages
36
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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

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

New Member
Joined
Feb 19, 2005
Messages
36
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

New Member
Joined
Feb 19, 2005
Messages
36

ADVERTISEMENT

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

Kbob

New Member
Joined
Feb 19, 2005
Messages
36
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

New Member
Joined
Feb 19, 2005
Messages
36
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,025
Messages
5,569,720
Members
412,289
Latest member
Kingchaos64
Top