I'm not sure if this a question for the Formulas & Functions forum or VBA/Macros forum - please point me in the right direction if you think this can't be solved with a formula/function?

I'm working to transform formal person names into marginally less formal names (essentially just dropping the Prefix). For various reasons, I can't have our data team export the info without the salutation, so I have to do that through Excel. The problem is that the "full name" form varies across individuals - for example, one person might be Mr. John T. Smith, III and another person might be Ms. Susan Jones. So when I use text-to-columns, I end up with anywhere from 3 to 6 columns of various name 'parts'. Any one column might contain first, middle, or last name info.

I was hoping I could devise a concatenate function that will auto concat along a row until it reaches a blank cell, which I could copy down the range of rows (so B2 + C2 for Susan Jones, but B2 + C2 + D2 + E2 for John T. Smith, III)

Does anyone have any ideas on how I might be able to set this up in Excel? Or does it just require a lot of 'manual' concatenating on my part?

Thank you!