Removing Spaces from a Field

How to remove a spaces from data within a field, either from the sides or from the middle

Case 1

The spaces are on either side of the data and any spaces in the middle should stay.
Example: ” Go To ” becomes “Go To”

Access QBE

In an update query, use this expression in “Update To”

Trim ([field1])

Case 2

The space are in the middle of the data and should be removed
Example: ” Go To ” becomes “GoTo”

MS SQL

use [database]
update [table “” not found /]

set [field1] = replace([field1],’ ‘,”)
go

Note that you must use the single quote. Also there is a space between the first pair of single quotes and there is nothing between the second pair of single quotes. So you are basically telling the system to replace a space with nothing.

Access QBE

In an update query, use this expression in “Update To”

Replace ([field1],” “,””)

Excel

=SUBSTITUTE([cell];” “;””)