Removing excess spaces in data

  • Thread starter Thread starter paulhk88
  • Start date Start date
P

paulhk88

I import data from spreadsheets into Access. Sometimes I get data that
contain excess spaces within a field such as description. For example: " AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM to remove
the leading and trailing spaces but how do I remove unnecessary spaces within
the text. I want to keep the one space between words. Thanks
 
On Thu, 25 Jun 2009 18:39:01 -0700, paulhk88

Check out the Replace function.

-Tom.
Microsoft Access MVP
 
Hi, this will remove all spaces. If there are more than one space between the
words, I want to remove the excess and keep only one so that it reads right.

Gina Whipp said:
paulhk88,

Try...

Replace ([YourField]," ","")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
I import data from spreadsheets into Access. Sometimes I get data that
contain excess spaces within a field such as description. For example: "
AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM to
remove
the leading and trailing spaces but how do I remove unnecessary spaces
within
the text. I want to keep the one space between words. Thanks
 
Looks like there are 7 spaces between fields so try this

Replace ([YourField]," "," ")


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
Hi, this will remove all spaces. If there are more than one space between
the
words, I want to remove the excess and keep only one so that it reads
right.

Gina Whipp said:
paulhk88,

Try...

Replace ([YourField]," ","")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
I import data from spreadsheets into Access. Sometimes I get data that
contain excess spaces within a field such as description. For example:
"
AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM to
remove
the leading and trailing spaces but how do I remove unnecessary spaces
within
the text. I want to keep the one space between words. Thanks
 
I import data from spreadsheets into Access. Sometimes I get data that
contain excess spaces within a field such as description. For example: " AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM to remove
the leading and trailing spaces but how do I remove unnecessary spaces within
the text. I want to keep the one space between words. Thanks

If the number of spaces is variable, you can run a query updating the field to

Replace([field], " ", " ")

replacing all double-blanks by a single blank, with a criterion of

LIKE "* *"

to limit it to records containing a double blank. Just run the query over and
over until it doesn't update anything.
 
Maybe I was not clear in my question. The spaces will be inconsistent in the
text - some text may have 7 spaces and some may have more or less.

I found a solution from another thread. It worked well for me and it is not
dependent on knowing how many excess spaces there are. This is from Duane
Hookom, a MS Access MVP. Hope it may help someone else... I attached extracts
from the solution below.

***********************************
1. A function to check each character in the string and remove excess spaces.

Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP

2. How to use it in a function

If you want to do it in the table then you should be able to open the table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window. Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.

UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

3. To clear out the multiple spaces of several fields with one query...

UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," "),
[NoFieldNameGiven2] = RemoveMultiples([NoFieldNameGiven2]," ")
WHERE [NoFieldNameGiven] Is Not Null and [NoFieldNameGiven2] Is Not Null;
Duane Hookom
Microsoft Access MVP
*************************************

Gina Whipp said:
Looks like there are 7 spaces between fields so try this

Replace ([YourField]," "," ")


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
Hi, this will remove all spaces. If there are more than one space between
the
words, I want to remove the excess and keep only one so that it reads
right.

Gina Whipp said:
paulhk88,

Try...

Replace ([YourField]," ","")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I import data from spreadsheets into Access. Sometimes I get data that
contain excess spaces within a field such as description. For example:
"
AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM to
remove
the leading and trailing spaces but how do I remove unnecessary spaces
within
the text. I want to keep the one space between words. Thanks
 
Thanks for posting your solution!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
Maybe I was not clear in my question. The spaces will be inconsistent in
the
text - some text may have 7 spaces and some may have more or less.

I found a solution from another thread. It worked well for me and it is
not
dependent on knowing how many excess spaces there are. This is from Duane
Hookom, a MS Access MVP. Hope it may help someone else... I attached
extracts
from the solution below.

***********************************
1. A function to check each character in the string and remove excess
spaces.

Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP

2. How to use it in a function

If you want to do it in the table then you should be able to open the
table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window.
Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.

UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

3. To clear out the multiple spaces of several fields with one query...

UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," "),
[NoFieldNameGiven2] = RemoveMultiples([NoFieldNameGiven2]," ")
WHERE [NoFieldNameGiven] Is Not Null and [NoFieldNameGiven2] Is Not Null;
Duane Hookom
Microsoft Access MVP
*************************************

Gina Whipp said:
Looks like there are 7 spaces between fields so try this

Replace ([YourField]," "," ")


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

paulhk88 said:
Hi, this will remove all spaces. If there are more than one space
between
the
words, I want to remove the excess and keep only one so that it reads
right.

:

paulhk88,

Try...

Replace ([YourField]," ","")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I import data from spreadsheets into Access. Sometimes I get data
that
contain excess spaces within a field such as description. For
example:
"
AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM
to
remove
the leading and trailing spaces but how do I remove unnecessary
spaces
within
the text. I want to keep the one space between words. Thanks
 
Back
Top