You have a space at the end of some of the rows that contain the zip code - you should use the TRIM function to get rid of them, like this, amending JeteMc's last formula:

The formula for column G is:
=IF(AND(B3<>"",D3<>""),VALUE(RIGHT(TRIM(A6),5)),IF(AND(B3<>"",D3=""),VALUE(RIGHT(TRIM(A5),5)),""))

Hope this helps.

Pete