One quick mention is that there are multiple more complicated formulas like the trim function with variations to pin point exact 0's to remove. Personally I do not use these as they are more niche to most situations but they can be useful. I will have a video on this shortly. Thank you!

Method 3 (pasting a formula into visible cells only) works perfectly. This will save me a lot of time and the hassle of accidentally pasting into hidden cells and overwriting other values. Multiple other "solutions" proposed on Microsoft support pages, KZfaq, reddit, etc were not working. This is a game changing solution to a problem that has been plaguing me.

Thank you bro