Combine 4 Columns Into One - Specail Address

[cem30]

House No. Street Prefix Street Name Street SFX Desire Results
100 W MAIN 100 W MAIN
101 WEST ST 100 WEST ST
E MAIN E MAIN
102 JACKSON 102 JACKSON


105 E JONES LANE 105 E JONES LANE

We have 4 Columns containing street address information we wish to combine into One Column

Each column could have a blank or null value. We also do not want to use blanks if the value is blank. We have found some information on blank values and merge but how do we check each field to produce the desire result.

[June7]

Maybe:

Trim(Replace(Field1 & " " & Field2 & " " & Field3 & " " & Field4), " ", " "))

The first pair of red quote marks has two spaces, the second has one space. This will eliminate any occurrence of double spaces due to empty fields.