how do I remove a space in a text value?

G

Guest

I have a field that contains 2 spaces in the Postal Code (i.e. A0A 0A0) and
I want to remove one space, so it would read A0A 0A0.

thanks
 
G

Guest

Create an update query with this as the UPdate To --
Replace([YourPostalCodeField], " ", " ")

The first set of quotes contain two spaces and the second has only one space.
 
F

fredg

Anywhere in the [PostalCode] field?
=Replace([PostalCode]," "," ")

Only if the double spaces are in the 4th and 5th position?
=IIf(inStr([PostalCode]," ") = 4,Left([PostalCode],3) & Mid([PostalCode],5)

Fred
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find Matching Zipcode Range for Address 3
Remove spaces text field 20
Removing Extra Space (with a twist) 2
Excel Hi, I want to add or remove spaces between initials in an Excel 2007 spreadsheet 0
Split column contents (de-concantenate) 7
Remove extra space in middle of text 1
Remove Space 6
Formatting output of a query - removing white space 4

Top