By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,120 Members | 1,799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,120 IT Pros & Developers. It's quick & easy.

How to remove spaces in text fields

P: 1
I'm extracting data from a database to create a table.

My table includes a text field 'Postcode' for postcodes.

Sometimes the extracted postcodes are in the format SL37HY and in other
instances a gap has been left somewhere in the data e.g. S L37HY or
SL3 7HY.

I want to validate the postcodes by matching them to a master table which
contains postcodes in the format SL37HY i.e. no spaces.

So......any advice....I need a simple way to edit my table and remove spaces from entries in the field 'Postcode' ??

Thanks in advance
May 3 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 218
Hi John

I would normally use an array of characters, basic idea being:-
  1. Loop thru the array looking for space character
  2. If found, save the array index where the space was found
  3. loop thru the remaining elements, moving them up one index
  4. Starting at the index you saved, loop thru the array looking for space character
  5. Go to 2
Of course you could also use a string and the Mid function, but you then have to get chunks of the string up to the Len of the string, which will change once a space is found

HTH
Steve
May 3 '07 #2

Expert 100+
P: 344
Hi John

I would normally use an array of characters, basic idea being:-
  1. Loop thru the array looking for space character
  2. If found, save the array index where the space was found
  3. loop thru the remaining elements, moving them up one index
  4. Starting at the index you saved, loop thru the array looking for space character
  5. Go to 2
Of course you could also use a string and the Mid function, but you then have to get chunks of the string up to the Len of the string, which will change once a space is found

HTH
Steve
This worked for me in the intermediate window
?replace("N13 4EH"," ","")
gave
N134EH

i.e. replace all spaces with nothing.
May 3 '07 #3

NeoPa
Expert Mod 15k+
P: 31,615
This worked for me in the intermediate window
Expand|Select|Wrap|Line Numbers
  1. ?Replace("N13 4EH"," ","")
gave
N134EH

i.e. Replace all spaces with nothing.
Lysander got my (therefore the best :D) answer in before me.

I find this one of the functions I use the most in my code (along with Nz() & IIf()).
May 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.