Separating a mixed length string

Hello and thank you for reading this,



I am an access user, but I am not at all adept in writing expressions and I have an issue I can't figure out and I have been searching the internet for days. I am very close...but not quite :)



Here's the scenario:



I have strings of names in a calculated field named [CurrentDelReverse] that are separated by spaces, but some strings have 2 names and some strings have 7 names.



They look like this:



tjcrane becca002

shiel001 donohue ekaler

jpohl jhalling jsleifer laerwin chan ekaler



I need to separate the names, but I only need the first two names in the field.



I am able to capture the first name with no problem using:



Delegatee: Left([CurrentDelReverse],InStr([CurrentDelReverse]," "))



My problem is trying to grab the second name in the list.



I have tried so many different things and the closest I got to solving it was:

Delegator: Trim(Mid([CurrentDelReverse],InStr(1,[CurrentDelReverse]," ")+1,IIf(InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")=0,0,InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")-InStr(1,[CurrentDelReverse]," "))))



This returned the second name in the list but ONLY if it had more than two names in the string.



I found and copied this code and put my field name in it hoping it would work, so I know somewhere in there something is telling it not to grab the name if there is only two, but I have no idea what that code even means so I have no idea how to change it. :banghead:



Any help will be GREATLY appreciated.

Attached Images

screenshot.jpg (97.8 KB)

Separating a mixed length string