How to create an array that outputs a "value" for each cell but skips empty cells

pretty much what the title is asking

I need to have this go through multiple sheets within a workbook also to compile a complete list

also the values in the cells are non numeric

here is the formula that i have that gives me the info for once cells output

Please Login or Register  to view this content.

ok how would i go about doing that?