Removing excess spaces in data

[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

 

[MGFoster]

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've found that the only way to do this is to run the string through a
routine that uses the Replace() function:

Function InnerTrim(ByVal str As String) As String

do
str = Replace(str, " ", " ") ' replace 2 spaces w/ 1 spc
until InStr(str, " ")=0 ' until there aren't 2 spaces

InnerTrim = str

End Function

You can make this a macro in Excel (the Excel Replace() function works a
little differently - see Excel's Help) or you can use the function in an
Access UPDATE like this:

UPDATE excel_import_table
SET spacey_string = Trim$(InnerTrim(spacey_string))
WHERE spacey_string LIKE "* *"

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkRAPIechKqOuFEgEQKafwCfRMJ+lq3hV2G3kuYxsElCMjhc8b8AnAm1
5hlZtZwWsQ3zFwk85TuqlQJo
=2uP9
-----END PGP SIGNATURE-----

 

[Tom van Stiphout]

On Thu, 25 Jun 2009 18:39:01 -0700, paulhk88

Check out the Replace function.

-Tom.
Microsoft Access MVP

 

[Gina Whipp]

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]

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

 

[Gina Whipp]

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

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

 

[John W. Vinson]

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.

 

[paulhk88]

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

*************************************

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

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

 

[Gina Whipp]

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

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

*************************************

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

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