1

While I can extract the first word from a cell containing multiple text values with error checking to return the only word if no multiple values exist. I cannot seem to wrap my brain around adding more checks (or if it is even possible in the same nested formula) for situations where some of the source cells contain a comma between multiple words. Example, the formula below will return "James" from "James Marriott". But, it returns "James," from "James, Marriott". If all of my cells in the range were consistent that would be easy, but they aren't. Attempts to nest multiple find statements have resulted in failure. Suggestions?

=IFERROR(LEFT(A1,FIND(" ",A2)-1),A2)

To compound matters, there are also cells that contain abbreviations as the first word, so somehow I need to account for that as well. For example "J.W. Marriott" where I need to apply the above logic to extract "Marriott".

Here are some examples below: 

Text             Desired output
James Marriott   James
James, Marriott  James
Able Acme        Able
Golden, Eagle    Golden
J.W. Marriott    Marriott
A.B. Acme        Acme
Sam Gilbert
  • 1,642
  • 3
  • 21
  • 38
twowheel
  • 11
  • 3
  • could you supply a few more examples of the different formats of text to be queried and the string that you would like to extract from them? – Sam Gilbert Feb 20 '16 at 14:09
  • Can't seem to format a comment so hope this is clear: For Situation #1 “James Marriott” desired result would be “James” “James, Marriott” desired result would be “James” “Able Acme” desired result would be “Able” “Golden, Eagle” desired result would be “Golden” For Situation #2 “J.W. Marriott” desired result would be “Marriott” “A.B. Acme” desired result would be “Acme” “Etc., Acme” desired result would be “Acme” – twowheel Feb 20 '16 at 14:43

1 Answers1

1

you could use regex (to set up please look at the post here)

Then you can extract the desired word with a formula like:

=regex(A1, "(?![Etc])[a-zA-Z]{2,}")

(This is searching for a pattern of two or more lower or upper case letters in the cell A1...and not searching for Etc)

Community
  • 1
  • 1
Sam Gilbert
  • 1,642
  • 3
  • 21
  • 38