Excel data set formatting issues

Question

I have a client data set I am struggling to organise. I was previously helped on here with a question on how to split a cell with words that had been formatted with no space. This worked successfully for the names of clients but now my data is a little more tricky in regards to their job title and company name as the variables are so different for each cell. For example;

Cell 1 - First Name
Cell 2 - Last Name
Cell 3 - ManagerCompanyName
or
Cell 3 - Principle ManagerCompany Name

My problem is with cell 3 and the spacing. I would like it to be as: Cell 3 - Manager Cell 4 - Company Name

Is there any way of doing this without manually adding the spaces?

The code for seperating first and last name was:

=SMALL(IFERROR(FIND({"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A2,2),""),1)

and then

=MID(F2, (G2), 60)

I presume it is similar, however the inconsistancy is making me struggle.

Thank you

Answer 1

=LEFT(A2,IFERROR(SMALL(FIND(CHAR(65+SEQUENCE(26,,0)),A2,2),1)-1,LEN(A2))) Can be used to get the first word up to the next capital.

=IFERROR(MID(A2,SMALL(IFERROR(FIND(CHAR(65+SEQUENCE(26,,0)),A2,2),""),1),LEN(A2)),"") To get the remainder from the 2nd found capital.

You can replace the CHAR(65+SEQUENCE()) part with the array you used if you don't have Office 365