Formula to parse first name & surname from cell containing single word

Just a tricky string manipulation

MAIN PROBLEM:
Say I have a list of persons names in Column A. I want formulas in Columns B & C to return the individuals First & Last names respectively. This is easy when the names are separated by a space/full stop/underscore but what if there is no separator?

So cell A1 "JohnSmith" would need to return "John" in cell B1 and "Smith" in cell C1.

I can think of two ways to approach this:

1. Identify first use of Upper case character where it is preceded by a lower case character
2. Identify the use of an Upper case character after the first letter in the string (to avoid picking up J in JohnSmith)

SUB PROBLEM:
Now a twist just to make it more challenging. What happens if we have the cell data as SurnameFirstName instead? The formulas would need to handle Surnames starting with "O'", "Mac" & "Mc"...


So does anyone have a solution to the main problem?

Maybe even the the sub problem as well?

Please attach a sample workbook with the expected output for better understanding

Originally Posted by :) Sixthsense :)

Please attach a sample workbook with the expected output for better understanding

Please see attached workbook.

Originally Posted by Armando Montes

Maybe:

Thanks for both tips. In this instance however I would prefer to avoid using VBscripts and arrays. :S

I am looking for non-array formulas for columns B & C.

Sorry folks. I know I have posed a hard one . Separating strings with no delimiters is not something I would do by choice!


Of interest:
I have found a UDF solution (see http://www.mrexcel.com/forum/excel-q...-delimiter.htm) and it works fine on my main problem. (It fails on the Secondary problem posed however).

Although the UDF solution is interesting, I am seeking a non-array formula solution that doesn't require any UDFs.

I found this formula but it is not quite there.

Formula:

Please Login or Register  to view this content.

On the main problem posted the formula:

1. fails on BobMcSurname.
2. inserts a space delimiter rather than parsing into two columns.