Share via

Vstack and Substitute

Anonymous
2023-10-19T05:21:44+00:00

I use below VSTACK formula to collect data from multiple worksheets that meets a specific date. I noticed that the blank fields in the Currency formatted columns show as 0. To fix this I used SUBSTITUTE. This does change the 0 to blank but it also converted all my formatting to text. How can I have blank fields show as blank fields and not as 0 without using SUBSTITUTE while keeping the formats intact?

SUBSTITUTE(FILTER(VSTACK(Angela:Zayda!G8:Y300),(VSTACK(Angela:Zayda!G8:G300)<>"")*(VSTACK(Angela:Zayda!R8:R300)=$I$1),"No Data"),"","")

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-10-19T05:29:00+00:00

You may try this one.

=IF(FILTER(VSTACK(Angela:Zayda!G8:Y300),(VSTACK(Angela:Zayda!G8:G300)<>"")*(VSTACK(Angela:Zayda!R8:R300)=$I$1),"No Data")=0,"",FILTER(VSTACK(Angela:Zayda!G8:Y300),(VSTACK(Angela:Zayda!G8:G300)<>"")*(VSTACK(Angela:Zayda!R8:R300)=$I$1),"No Data"))

This formula checks if the value returned by filter is 0, and if it is, it returns a blank cell. If it's not blank, it returns the value with the original one.

Was this answer helpful?

3 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-19T13:58:28+00:00

    You are welcome. You may also give feedback on it.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-10-19T15:25:13+00:00

    Thank you:)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-10-19T15:18:49+00:00

    I did.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-10-19T13:46:07+00:00

    You may try this one.

    =IF(FILTER(VSTACK(Angela:Zayda!G8:Y300),(VSTACK(Angela:Zayda!G8:G300)<>"")*(VSTACK(Angela:Zayda!R8:R300)=$I$1),"No Data")=0,"",FILTER(VSTACK(Angela:Zayda!G8:Y300),(VSTACK(Angela:Zayda!G8:G300)<>"")*(VSTACK(Angela:Zayda!R8:R300)=$I$1),"No Data"))

    This formula checks if the value returned by filter is 0, and if it is, it returns a blank cell. If it's not blank, it returns the value with the original one.

    Hi Snow,

    This is excellent. It works like a charm. Thank you very much.

    Was this answer helpful?

    0 comments