Extract Limited Single Word from Alphanumeric

[wckek]

• Hi Guys, I need help in the complex scenario.
• I have database of names, unfortunately, there was no proper way on the text input.
• So I need to extract any single word name from each cells.
• The cell will include more than 1 words, will have numbers (which need to be excluded), and limit the number of characters for 15.
• Sample names;
• Johnn English
• Jhonny ENGLISH
• JOHN ENGLISH
• 12345 JO English
• etc
• Formula will check each words in a name, if the number of characters only 2, it will go to the next word of the name. Example: 'Mr John' = Formula will recognize 'John'
• Formula will recognize up to 6 words in a name, should it have 7, Formula will return the First Word. Example: 'First Second Third Fourth Fifth Sixth Seventh' = Formula will return 'First'
• Formula will also ensure that the name mentioning is 'Proper', which means it will no return all CAPS name. Example: 'JOHN' = Formula will change to 'John'
• Formula will return the single name should there be no last name. Example: 'John' = Formula will return 'John'
• Formula will restrict the number of characters in the name to be 15. Example: 'JhonnyEnglishReborn' = Formula will return 'JhonnyEnglis'

I have created formula below, but unfortunately it recognizes numbers;
=LEFT(PROPER(IFERROR(IF(LEN(LEFT(B1,FIND(" ",B1)-1))>2,LEFT(B1,FIND(" ",B1)-1),IF(LEN(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(2-1)*LEN(B1)+1,LEN(B1))))>2,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(2-1)*LEN(B1)+1,LEN(B1))),IF(LEN(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(3-1)*LEN(B1)+1,LEN(B1))))>2,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(3-1)*LEN(B1)+1,LEN(B1))),IF(LEN(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(4-1)*LEN(B1)+1,LEN(B1))))>2,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(4-1)*LEN(B1)+1,LEN(B1))),IF(LEN(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(5-1)*LEN(B1)+1,LEN(B1))))>2,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(5-1)*LEN(B1)+1,LEN(B1))),IF(LEN(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(6-1)*LEN(B1)+1,LEN(B1))))>2,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",LEN(B1))),(6-1)*LEN(B1)+1,LEN(B1))),LEFT(B1,FIND(" ",B1)-1))))))),B1)),15)

Can somebody help?
Many many thanks in advance.

 

[Gingertrees]

Whoo boy, that's a lot of things for one formula. I suggest using a macro for the various steps here. A little more information first:
1) how are the cells arranged? Is it one cell in A2, another in A3, A4, etc. (down the column)? Or is it A2, B2, C2, etc.? Or something else?
2) I assume you are hoping to remove titles (Ms, Mr, Mrs, etc.), but if the person goes by "Jo", you'd want "Jo" kept?
3) What output are you looking for? A column of names?

 

[wckek]

Hi and thanks for replying.
I need it to be formula, as it will be distributed to those in need to use the database also.
1. it's A1,A2,A3,A4,..
2. Fortunately, there would be no title of Mr, Ms, and so on
3. Yes, a column of names next to the original names.

Whoo boy, that's a lot of things for one formula. I suggest using a macro for the various steps here. A little more information first:
1) how are the cells arranged? Is it one cell in A2, another in A3, A4, etc. (down the column)? Or is it A2, B2, C2, etc.? Or something else?
2) I assume you are hoping to remove titles (Ms, Mr, Mrs, etc.), but if the person goes by "Jo", you'd want "Jo" kept?
3) What output are you looking for? A column of names?

 

[Joe4]

I would recommend creating your own function to do what you want in VBA (User Defined Function).
Then it can be used like any other Excel function on your sheet.

See here: http://www.cpearson.com/excel/writingfunctionsinvba.aspx