FrankieGTH
New Member
- Joined
- May 29, 2021
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I'm using the Filter function on a table that has blank cells. When I use the function, blank cells return as 0. Ideally, I'd like the 0s not to be there and the filter returns blank cells as blank.
In an epic fail, I tried to use the substitute function, substituting "0" for "", however this chops off 0s from the hours column (30 becomes 3, etc). It feels that should be a way to do this, but my brain is failing. Would anyone be able to conjure up a formula to replace these 0s? I'd like to avoid using conditional formatting if possible.
Sincerest thanks.
I'm using the Filter function on a table that has blank cells. When I use the function, blank cells return as 0. Ideally, I'd like the 0s not to be there and the filter returns blank cells as blank.
In an epic fail, I tried to use the substitute function, substituting "0" for "", however this chops off 0s from the hours column (30 becomes 3, etc). It feels that should be a way to do this, but my brain is failing. Would anyone be able to conjure up a formula to replace these 0s? I'd like to avoid using conditional formatting if possible.
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Staff | Department | Fire Trained | Hours Per Week | Pura | childrenswear | No | 17 | |||
2 | Pura | childrenswear | No | 17 | Leora | childrenswear | Yes | 25 | |||
3 | Leora | childrenswear | Yes | 25 | Audrey | childrenswear | 0 | 0 | |||
4 | Audrey | childrenswear | Marti | childrenswear | Yes | 22 | |||||
5 | Gwenda | womenswear | No | 10 | Maryetta | childrenswear | Yes | 18 | |||
6 | Janee | menswear | No | 28 | Salome | childrenswear | 0 | 20 | |||
7 | Warren | menswear | No | 17 | Regenia | childrenswear | Yes | 26 | |||
8 | Shantel | menswear | No | 20 | Yung | childrenswear | 0 | 30 | |||
9 | Rachel | menswear | Yes | 29 | |||||||
10 | Marti | childrenswear | Yes | 22 | Pura | childrenswear | No | 17 | |||
11 | Maryetta | childrenswear | Yes | 18 | Leora | childrenswear | Yes | 25 | |||
12 | Branda | womenswear | Yes | 19 | Audrey | childrenswear | |||||
13 | Boyd | menswear | No | 26 | Marti | childrenswear | Yes | 22 | |||
14 | Vada | menswear | Yes | 29 | Maryetta | childrenswear | Yes | 18 | |||
15 | Lowell | menswear | No | 25 | Salome | childrenswear | 2 | ||||
16 | Kristina | menswear | Yes | 14 | Regenia | childrenswear | Yes | 26 | |||
17 | Becki | womenswear | Yes | 36 | Yung | childrenswear | 3 | ||||
18 | Salome | childrenswear | 20 | ||||||||
19 | Loura | womenswear | No | 26 | |||||||
20 | Regenia | childrenswear | Yes | 26 | |||||||
21 | Yung | childrenswear | 30 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F1:I8 | F1 | =FILTER($A$1:$D$21,$B$1:$B$21="childrenswear") |
F10:I17 | F10 | =SUBSTITUTE(FILTER($A$1:$D$21,$B$1:$B$21="childrenswear"),"0","") |
Dynamic array formulas. |
Sincerest thanks.