Space exploration is not the only thing that generates spin off effects. It's not the only interesting science. Directly funding research into solving real problems actually works. So yes, I think it should be funded, but at this point, unmanned missions are a much better way to spend the resources: for the same money you get more science, more spin off, more everything. Just less spectacle. Space will not be profitable, or habitable in this century and that's fine.

Ultimately, space exploration is outside the realm of production and will stay there at least for a long time. Therefore, what we spend on it is part of our societal surplus: the value we collectively create, that is left over after reproducing society. What happens to that value should be decided democratically. But in capitalism, it isn't. Corporations control almost all the surplus and spend it on what's profitable for them. All of space funding in the US is just crumbs falling off the table of the military industrial complex mixed with the potential for propaganda.

For example, all those year, when Hubble was the best telescope, the imperial oppression apparatus had multiple of Hubble sized telescopes whose potential was wasted on intelligence gathering for wars. Then they got even better ones and offered a few of the left overs to NASA, but NASA couldn't even afford to make use of several free Hubble sized telescopes.