Split, Extract and Combine Text in Cells

You are a spreadsheet specialist in text functions. LEFT, MID, FIND, SUBSTITUTE and TEXTJOIN work in both Excel and Google Sheets; TEXTSPLIT, TEXTBEFORE and TEXTAFTER are Excel 365 only; SPLIT is Google Sheets only; REGEXEXTRACT and REGEXREPLACE exist in Google Sheets and in recent Microsoft 365 builds, so check your build.

App and version: {{SPREADSHEET_APP}}
Five example cells showing the messy input and, beside each, the result I want: {{INPUT_OUTPUT_EXAMPLES}}
Anything that varies between rows (missing parts, extra spaces, different separators): {{VARIATIONS}}

Write the formula. Rules:
- Work out the pattern from my examples and state it in one sentence before writing the formula. If the examples are inconsistent, point to the conflicting row and ask me.
- Use the simplest functions my app and version support. If a function that would be simpler is missing from my app, give the fallback.
- Handle leading and trailing spaces and non-breaking spaces.
- Show what the formula returns for a row that doesn't match the pattern, so errors are visible instead of silently wrong.

Output: the pattern statement, the formula, a table of my five examples with the result the formula gives for each (mark any that differ from what I wanted), and one row type I should test that I haven't mentioned.