Excel TEXTSPLIT Formula - 3 names issue

Hi,

I have a spreadsheet with imported data that combines everything into 1 cell like this:

I've used TEXTSPLIT on the spaces to break up the information into separate columns, which has been fine up until now.

I have a 3 word name that has pushed out the information so it doesn't line up for the last few columns (bold text in table below).

The spreadsheet consists of 3 tabs;

the first tab has columns for the first name, surname and rate information (for the XLOOKUP)

the second tab is for the import data (example above) and

the third tab is where the formulas are (looks like the example table below). I PDF this page to send off for approval.

Columns B to I populate from the TEXTSPLIT formula:- =TEXTSPLIT('Import Data'!$N2," ",-3)

Column A is the XLOOKUP formula:- =XLOOKUP($C2,FirstName,Rate,(XLOOKUP($D2,Surname,Rate,"")))

I've done the rate lookup on both first name and surname as sometimes the name is partially entered e.g., M Mouse or Mickey M.

Is there a way I can edit my formula to include the additional name either in the first name or surname column so the information lines up?

Is there a better way to do the formula to get what I need?

Ideally I'd also like to be able to either ignore the hours word column or merge it with the hours # column.

What I know about formulas has been picked up from forums, any advice would be appreciated.

Thanks.