Learn Spanish (Latin America): ¡Qué horror! El ruido de la calle es terrible.

Yes, literally it means "What a horror!" In Spanish, exclamations use ¡Qué... ! followed by either an adjective (like ¡Qué terrible!) or a noun (like ¡Qué horror!). While English naturally says "How awful!" using an adjective, Spanish very often uses a noun for this specific reaction.

Why do we say el ruido de la calle instead of combining the words into something like la calle ruido?

In English, it's common to put one noun directly in front of another to describe it, like "street noise." Spanish grammar doesn't allow this noun-stacking. Instead, you almost always have to use de (of) to link them, literally saying "the noise of the street" (el ruido de la calle).

Why does this sentence use es instead of está? Isn't the street noise happening temporarily right now?

We use es (from the verb ser) because we are describing a core characteristic of the noise itself. Even if the noise will eventually stop, its nature is terrible. You use ser to describe the fundamental qualities of a person, place, or thing.

Would the word terrible change if we were describing a feminine noun instead of el ruido?