extract name based on condition

[darkhangelsk]

hi,

is there a way that i can choose what to extract in a certain name? let say the name is Ma. angela jones, i dont want to extract the "Ma." so ill extract "jones". the other sample is Jones, Rick C. so i'll extract jones not "c.". bottom line is i'm not gonna extract if only 2 or 1 letter/s in the first or last cell.


thanks for help

[L-Drr]

what happened to "angela" and "rick"??

Please explain clearly what data you have and what you want the result to be, by giving explicit examples of pre- and post- solution

[darkhangelsk]

hi L-Drr!

Sorry for the confusion. Let say if cell A1 is "Ma. angela jones" and i want to extract the first name so 'ill use a formula in B1 "=LEFT(A1,FIND(" ",A1,1)-1)". But since the result is only 2 letters (Ma.), instead of extracting the first name ill extract the last name to get "jones". forget "angela".

i have a list of names in A1. the problem is, there's a lot of names with "Ma.", or the middle name is in the first position (C. Rick, Jones) that's why i want this formula so there would be no duplicate once i extract the names.

thanks you so much!

[L-Drr]

So to rephrase: the formula should result in the first piece of text (up to the first space), unless this is 3 characters or less, in which case the result should be the last piece of text (from the last space untill the end). Am I right?

[hemesh]

Can you please upload a sample work book

[darkhangelsk]

here's the sample workbook for reference. thanks to both of you!

[FDibbins]

Based on your sample data, use this, copied down...
=IF(SEARCH(" ",A2,1)<=4,MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,99),LEFT(A2,SEARCH(" ",A2,1)-1))

[L-Drr]

Based on your sample data, use this, copied down...
=IF(SEARCH(" ",A2,1)<=4,MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,99),LEFT(A2,SEARCH(" ",A2,1)-1))

Exactly what I was thinking of except for the MID(second space, 99 characters) I would have gone for RIGHT(LEN minus second space), but I guess it's pretty safe to say there are no Phillipine namnes with more than 99 characters