Data Seperation by InStr()

[A S MANN]

I have 1 field that is Combination of Three names Last First Middle
eg A B C Seperated by " " ( eg A & " " & B & " " & C )

I want A B & C separately in a form from a field.
To Get A seperatly I used =Left([Field],Instr(1, [Field], " ") -1)
To Get B seperatly I Used =Mid([Field], Instr(1, [Field], " ") + 1)
But I am Getting B C
To Get C seperatly I Used =Right([Field],1)


C is only one Charractor.
the Second Code is wrong. Can you please guide what went wrong? I need only B And not B C?
Thanks in advance

[pdebaets]

What if the last name or first name has a space in it?

[pdebaets]

I have some string functions at this site that may help:http://peterssoftware.com/strfn.htm

to get "C" use =Right([Field],1)

to get "B" use =xg_GetLastWord(left([Field],len([Field])-2))

to get "A" use =Trim(Mid([Field], 1, InStr([Field], xg_GetLastWord(Left([Field], Len([Field]) - 2))) - 1))

The xg_GetLastWord function is in the string functions example database above.

This should handle last names with spaces as well.

[A S MANN]

Thanks
I got the Second String by MsgBox xg_GetSubString() fuction
Thanks for help