Middle of String Parsing Query

[ljconnolly8]

I need the unit # out of the following string:
MEDICAL CENTER CONDO UNIT 203 DES IN ORB 1360 PG 325

Trim(Right([legal_description],Len([legal_description])-(InStr([legal_description],"unit")+4)))

Gives me: 203 DES IN ORB 1360 PG 325

How do I get rid of the rest? "DES IN ORB 1360 PG 325"

The "unit #" is not always in the same position in the string.

I know I need to use the MID function and probably relate to a space " ". It's been 3 yrs since I took an Access class and I have had no luck in my online searches.

Thanks