MS Access – Separate Number from a combined Number Street Field

For the Street Number

Left([No_and_Street_Field],InStr([No_and_Street_Field]," ")-1)

For the Street

Right([No_and_Street_Field],Len([No_and_Street_Field])-InStr([No_and_Street_Field]," "))