Extracting a person's name from a string of text using IF/Else

[Terrakiin]

Hey, I need to extract a name from a string in a format of [Numbers with SC] [ Numbers with SC] [Name] [LOCATION] [Alphanumeric] (Eg: 63/12-3 218391/23457-1892 Adam Smith NEW YORK BL12938LR12). What I initially thought of doing was use a MID Function to remove the beginning numeric values and special characters from the string and then use a LEFT function to search for the Location and remove everything including and after the location. [=LEFT(C3,(FIND(" NEW YORK",C3,1)-1))].

Issue is there's a lot of locations so I had to filter out each location then run the next location in the chain and repeat that process until all locations were accounted for. There has to be a faster way.

My thoughts now is to use an IF/Else function where it searches for a Location from a list that I give it and IF it finds the location it runs the appropriate LEFT function. Else it moves onto the next location.

Is this possible? If it is, is there a better/faster way? Thank you.

Also I have almost no experience in coding or macros.

[AlphaFrog]

This formula extracts two words (names) starting from the second space character to the forth space character.

Please Login or Register  to view this content.

If you had a three word name name like George Van Patten, it would only extract George Van.

[Terrakiin]

This formula extracts two words (names) starting from the second space character to the forth space character.

Please Login or Register  to view this content.

If you had a three word name name like George Van Patten, it would only extract George Van.

That sounds better than we what I’m doing already. Though, Im dealing with around 2-3 thousand names so I don’t think I’d be able to find out whether I got the full names of everyone.

[AlphaFrog]

Will the LOCATION always be in caps as in your example data?

[Terrakiin]

Yes. The location is always in caps

[AlphaFrog]

The code below extracts the name between the second space character to the word in all caps.

Please Login or Register  to view this content.