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

White Space!

P: n/a
JA
Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has been
cut-n-pasted from online forms, and the results can be VERY spread out. I
could probably save 80% of the space if I could get rid of the extra white
space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of a
field, and there are actually 8 spaces, the program will go through it again
and again, that 1 or 2 at a time, until there are no more spaces. But that
doesn't seem to work with my problem.

Any help would be appreciated! Oh, and I'm using Access 2000.

Thanks, Jill~
Oct 5 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Do you do VBA code? It would certainly be possible to write code that would
go through and eliminate the extra spaces. In HTML, a <BRmarkup means
break to a new line, but it's not the only markup that does.

If you have a recent version of Access, check Help on the Replace
function... for replacing two spaces " " with one space " ". If you have
an earlier version, search the archives of this newsgroup for one of many
procedures/functions that replaced one string of characters with another.
You can use it in an Update Query to clean up multiple Fields at a time.

Larry Linson
Microsoft Access MVP

"JA" <ja*****@kc.rr.comwrote in message
news:t4************@tornado.rdc-kc.rr.com...
Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has
been cut-n-pasted from online forms, and the results can be VERY spread
out. I could probably save 80% of the space if I could get rid of the
extra white space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of
a field, and there are actually 8 spaces, the program will go through it
again and again, that 1 or 2 at a time, until there are no more spaces.
But that doesn't seem to work with my problem.

Any help would be appreciated! Oh, and I'm using Access 2000.

Thanks, Jill~

Oct 6 '06 #2

P: n/a
JA wrote:
Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has been
cut-n-pasted from online forms, and the results can be VERY spread out. I
could probably save 80% of the space if I could get rid of the extra white
space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of a
field, and there are actually 8 spaces, the program will go through it again
and again, that 1 or 2 at a time, until there are no more spaces. But that
doesn't seem to work with my problem.
You could write queries to do the text replacement or you could write code
to open up each table, look at each text field and modify it. Either way
you'll need a function to take the original value and return the cleaned
value.

The "mark" you are referring to might be an ASCII 160. This is found in HTML
code as the non-breaking space ( &nbsp; or   ) that some browsers support.

If you are viewing this in a browser then here is the non-breaking space:
( &amp;nbsp; or &amp;#160; )

http://webdesign.about.com/library/bl_htmlcodes.htm
http://en.wikipedia.org/wiki/Charact...odings_in_HTML

The code below can be modified to remove those characters.

Here are a couple of possible starting points. Apologies to the original
author on the Substitute() proc; no indication of who it was.

? Substitute("this is a good example."," ", " ")
this is a good example.

Code:

' supports using a negative number to get the count of the sTextToReplace starting from
the right
' side of sFullText. Examples are:
'
' Print Substitute("this is a test", "is", "are")
' thare are a test
' Print Substitute("this is a test", "is", "are", 1)
' thare is a test
' Print Substitute("this is a test", "is", "are", 2)
' this are a test
' Print Substitute("this is a test", "is", "are", -1)
' this are a test
' Print Substitute("this is a test", "is", "are", -2)
' thare is a test
Public Function Substitute(sFullText As String, _
sTextToReplace As String, _
sReplacementText As String, _
Optional nOccurence As Long) As String

Dim nLocation As Long, nCount As Long

Substitute = vbNullString

If Len(sFullText) = 0 Or Len(sTextToReplace) = 0 Then
Exit Function
End If

nCount = 1

If nOccurence >= 0 Then
nLocation = InStr(sFullText, sTextToReplace)
ElseIf nOccurence < 0 Then
nLocation = InStrRev(sFullText, sTextToReplace)
End If

Do While nLocation 0

If nOccurence = 0 Then
sFullText = Left$(sFullText, nLocation - 1) & sReplacementText & _
Mid$(sFullText, nLocation + Len(sTextToReplace))
nLocation = InStr(sFullText, sTextToReplace)

ElseIf nOccurence 0 Then
If nCount = nOccurence Then
sFullText = Left$(sFullText, nLocation - 1) & sReplacementText & _
Mid$(sFullText, nLocation + Len(sTextToReplace))
Exit Do
End If

nLocation = InStr(nLocation + 1, sFullText, sTextToReplace)
nCount = nCount + 1
Else ' nOccurence < 0 Then
If nCount = Abs(nOccurence) Then
sFullText = Left$(sFullText, nLocation - 1) & sReplacementText & _
Mid$(sFullText, nLocation + Len(sTextToReplace))
Exit Do
End If

nLocation = InStrRev(Left(sFullText, nLocation - 1), sTextToReplace)
nCount = nCount + 1
End If
Loop

Substitute = sFullText

End Function

------------------------------------------------------------

? removeMultipleSpaces("this is a good example.")
this is a good example.

Code:

Public Function removeMultipleSpaces(sIn As String) As String
Dim x As Long, bSpacesStateOn As Boolean, sChar As String

sIn = Trim(sIn) ' remove beginning or trailing spaces

For x = 1 To Len(sIn)
sChar = Mid(sIn, x, 1)
If sChar = Chr(32) Then
If bSpacesStateOn Then
' do nothing (don't append)
Else
removeMultipleSpaces = removeMultipleSpaces & sChar
bSpacesStateOn = True
End If
Else
removeMultipleSpaces = removeMultipleSpaces & sChar
bSpacesStateOn = False
End If
Next x

End Function
--
'---------------
'John Mishefske
'---------------
Oct 6 '06 #3

P: n/a
JA wrote:
Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has been
cut-n-pasted from online forms, and the results can be VERY spread out. I
could probably save 80% of the space if I could get rid of the extra white
space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of a
field, and there are actually 8 spaces, the program will go through it again
and again, that 1 or 2 at a time, until there are no more spaces. But that
doesn't seem to work with my problem.

Any help would be appreciated! Oh, and I'm using Access 2000.

Thanks, Jill~
You may be able to use these:

Public Function RemoveLineFeeds(ByVal InputString$)
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
InputString = Trim(InputString)
With RE
.Global = True
.IgnoreCase = True
.Pattern = vbNewLine
RemoveLineFeeds = .Replace(InputString, " ")
End With
Set RE = Nothing
End Function

Public Function RemoveDoubleSpaces(ByVal InputString$)
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
InputString = Trim(InputString)
With RE
.Global = True
.IgnoreCase = True
.Pattern = "\s{2,}"
RemoveDoubleSpaces = .Replace(InputString, " ")
End With
Set RE = Nothing
End Function

Sub test()
Debug.Print RemoveDoubleSpaces(RemoveLineFeeds(" Lyle" &
vbNewLine & " Fairfield"))
' shows Lyle Fairfield
End Sub

If you do it's probably a good idea to remove the line feeds first as
the test example shows.

Oct 6 '06 #4

P: n/a
My Replace implementation is this:

Function Replace(cOut As String, cIn As String, cSource As Variant) As
String
Dim nOut As Long
Dim nIn As Long
Dim nPos As Long
Dim cRes As String
If IsNull(cSource) Then
cRes = ""
Else
nOut = Len(cOut)
nIn = Len(cIn)
cRes = cSource
nPos = InStr(cRes, cOut)
Do Until nPos = 0
cRes = Left(cRes, nPos - 1) & cIn & MID(cRes, nPos + nOut)
nPos = InStr(nPos + nIn, cRes, cOut)
Loop
End If
Replace = cRes
End Function

Larry Linson schreef:
Do you do VBA code? It would certainly be possible to write code that would
go through and eliminate the extra spaces. In HTML, a <BRmarkup means
break to a new line, but it's not the only markup that does.

If you have a recent version of Access, check Help on the Replace
function... for replacing two spaces " " with one space " ". If you have
an earlier version, search the archives of this newsgroup for one of many
procedures/functions that replaced one string of characters with another.
You can use it in an Update Query to clean up multiple Fields at a time.

Larry Linson
Microsoft Access MVP

"JA" <ja*****@kc.rr.comwrote in message
news:t4************@tornado.rdc-kc.rr.com...
>Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has
been cut-n-pasted from online forms, and the results can be VERY spread
out. I could probably save 80% of the space if I could get rid of the
extra white space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of
a field, and there are actually 8 spaces, the program will go through it
again and again, that 1 or 2 at a time, until there are no more spaces.
But that doesn't seem to work with my problem.

Any help would be appreciated! Oh, and I'm using Access 2000.

Thanks, Jill~

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
Oct 6 '06 #5

P: n/a
Regular Expressions are Greedy. That means that when we ask a Regular
Expression to deal with an expression, say, "<" and any number of
characters and ">" it finds the largest of such expressions first.
For example in
<br><p style="font-size:larger">
it finds the whole string first, not <brfirst. This makes Regular
Expressions not so great for removing html tags, unless our particular
Regular Expressions have a switch to set Greedy off.
But Greedy is advantageous when reducing strings of multiple spaces to
one space. If we look for strings of two spaces with VBA then we find
only strings of two spaces and must repetitively replace these in a
loop as you have coded.
But this pattern, \s{2,}, (or /\s{2,}/) says, find two or more spaces.
So, say, five spaces, are dealt with all at once, rather than requiring
multiple passes of two each. (and of course it finds any white space,
not just Chr$(32).)
I don't know the innards of Replace. Sometimes I think Replace is built
on Regular Expressions; if that's the case maybe behind the scenes
Replace is Greedy, but that is not documented TTBOMK.
I have found over the past several years that the use of Regular
Expressions has made my coding more powerful when manipulating strings.
When one moves between languages it's quite easy to move functions
based on Regular Expressions, as syntax is usually almost identical.
And of course, Regular Expressions are super-fast.
I recommend them

Bas Cost Budde wrote:
My Replace implementation is this:

Function Replace(cOut As String, cIn As String, cSource As Variant) As
String
Dim nOut As Long
Dim nIn As Long
Dim nPos As Long
Dim cRes As String
If IsNull(cSource) Then
cRes = ""
Else
nOut = Len(cOut)
nIn = Len(cIn)
cRes = cSource
nPos = InStr(cRes, cOut)
Do Until nPos = 0
cRes = Left(cRes, nPos - 1) & cIn & MID(cRes, nPos + nOut)
nPos = InStr(nPos + nIn, cRes, cOut)
Loop
End If
Replace = cRes
End Function
Oct 6 '06 #6

P: n/a
Perhaps I exaggerated the difficulty of removing html tags with Regular
Expressions. This pattern may/should/seems to work:

Public Function RemoveHTMLTags(ByVal InputString$)
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
InputString = Trim(InputString)
With RE
.Global = True
.IgnoreCase = True
.Pattern = "\<(.|\n)*?\>"
RemoveHTMLTags = .Replace(InputString, "")
End With
Set RE = Nothing
End Function

Oct 6 '06 #7

P: n/a
There's always something to be learned in CDMA. Thanks, Lyle.

Larry

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
Perhaps I exaggerated the difficulty of removing html tags with Regular
Expressions. This pattern may/should/seems to work:

Public Function RemoveHTMLTags(ByVal InputString$)
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
InputString = Trim(InputString)
With RE
.Global = True
.IgnoreCase = True
.Pattern = "\<(.|\n)*?\>"
RemoveHTMLTags = .Replace(InputString, "")
End With
Set RE = Nothing
End Function

Oct 6 '06 #8

P: n/a
JA
Oh boy! Thanks for all the great answers, but they are mostly way over my
dumb head!

For this one:
for replacing two spaces " " with one space " ".

I've done that. It got rid of 40 instances (in my one test record). I used
other amounts of spaces, other than two. I used 3, 4, 10, 42, etc. etc. Some
got rid of as many as 1500 occurrences. I would think that -finding 2 spaces
and replacing it with 1 - would loop through until they were all gone, but
it doesn't. And there is no set amount of spaces (and lines) between text.

Also, it's not the html tags that I am trying to replace or get rid of.

I think what I was looking for was a way to remove a sort of "hidden"
end-of-line mark. Like MS Word has something, I think it might be ^p, you
can find and replace that, and it will get rid of white lines:
A line of text here (^p hidden)
(^p hidden)
A line of text here(^p hidden)
(^p hidden)
A line of text here(^p hidden)
(^p hidden)
BECOMES:

A line of text here
A line of text here
A line of text here
OR EVEN:
A line of text here A line of text here A line of text here

I was thinking that since Access and Word are "sister" apps, maybe Access
has something similar?



"JA" <ja*****@kc.rr.comwrote in message
news:t4************@tornado.rdc-kc.rr.com...
Is there a way to remove all the white space in the fields?

I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces
and replacing them with none. I don't want to replace single spaces, those
are the spaces between the words. But most of what is in the fields has
been cut-n-pasted from online forms, and the results can be VERY spread
out. I could probably save 80% of the space if I could get rid of the
extra white space.

It contains html tags, so anything more than one space is wasted anyway.

Is there a way to remove the end of the line "mark" (whatever that would
be!), so the lines can be continuous? And to remove any amount of spaces
larger than 1 space?

I know that if you find and replace like 1 or 2 spaces at the beginning of
a field, and there are actually 8 spaces, the program will go through it
again and again, that 1 or 2 at a time, until there are no more spaces.
But that doesn't seem to work with my problem.

Any help would be appreciated! Oh, and I'm using Access 2000.

Thanks, Jill~

Oct 6 '06 #9

P: n/a
"JA" <ja*****@kc.rr.comwrote
Also, it's not the html tags that I am trying to replace or get rid of.

I think what I was looking for was a way to remove a sort of "hidden"
end-of-line mark. Like MS Word has something, I think it might be ^p, you
can find and replace that, and it will get rid of white lines:
No, Access does not have "markup" that can be "hidden" as does Microsoft
Word. In fact, you need an ActiveX Control to be able to use Rich Text
formatting for your data. That would have some kind of markup to control
formatting, but I am not "into" Rich Text Files.

Just regular Access text will have Chr$(13) & Chr$(10), a combination of
Carriage Return and Line Feed, or just a Line Feed to cause a new line. If
a recent version using Unicode, it'll have the Unicode equivalent of those
ASCII codes, most likely the one byte of actual ASCII data prefixed by
zeroes to fill out the character.

Oct 7 '06 #10

P: n/a
JA
Larry,

Is there a way to remove Chr$(13) & Chr$(10)?

"Larry Linson" <bo*****@localhost.notwrote in message
news:_aGVg.3796$Ed5.2884@trnddc03...
"JA" <ja*****@kc.rr.comwrote
Also, it's not the html tags that I am trying to replace or get rid of.

I think what I was looking for was a way to remove a sort of "hidden"
end-of-line mark. Like MS Word has something, I think it might be ^p,
you
can find and replace that, and it will get rid of white lines:

No, Access does not have "markup" that can be "hidden" as does Microsoft
Word. In fact, you need an ActiveX Control to be able to use Rich Text
formatting for your data. That would have some kind of markup to control
formatting, but I am not "into" Rich Text Files.

Just regular Access text will have Chr$(13) & Chr$(10), a combination of
Carriage Return and Line Feed, or just a Line Feed to cause a new line.
If a recent version using Unicode, it'll have the Unicode equivalent of
those ASCII codes, most likely the one byte of actual ASCII data prefixed
by zeroes to fill out the character.

Oct 8 '06 #11

P: n/a
If using a recent version of Access, use the Replace function. If an earlier
version, say Access 97 or earlier, you'll need to search the archives for
one of many Replace procedures that people posted.

You can write a user-defined-function to include in a Query, similar to the
following:

Function RemCRLF(pstrText As String) As String
Dim strWorking As String
strWorking = Replace(pstrText, vbCrLf, "")
strWorking = Replace(strWorking, vbCr, "")
strWorking = Replace(strWorking, vbLf, "")
RemCRLF = strWorking
Exit Function

This can be executed either from code or in a Query against the Fields for
which you want the line-breaks removed. Just using the first Replace will
work for Access-generated text, but the additional calls make it work for
Text created in other sources which may use only the Carriage Return or the
LineFeed character.
Larry Linson
Microsoft Access MVP

"JA" <ja*****@kc.rr.comwrote in message
news:CD****************@tornado.rdc-kc.rr.com...
Larry,

Is there a way to remove Chr$(13) & Chr$(10)?

Oct 8 '06 #12

P: n/a
JA
Thanks, Larry, I will try that.
"Larry Linson" <bo*****@localhost.notwrote in message
news:6CaWg.800$Gp4.726@trnddc08...
If using a recent version of Access, use the Replace function. If an
earlier version, say Access 97 or earlier, you'll need to search the
archives for one of many Replace procedures that people posted.

You can write a user-defined-function to include in a Query, similar to
the following:

Function RemCRLF(pstrText As String) As String
Dim strWorking As String
strWorking = Replace(pstrText, vbCrLf, "")
strWorking = Replace(strWorking, vbCr, "")
strWorking = Replace(strWorking, vbLf, "")
RemCRLF = strWorking
Exit Function

This can be executed either from code or in a Query against the Fields for
which you want the line-breaks removed. Just using the first Replace will
work for Access-generated text, but the additional calls make it work for
Text created in other sources which may use only the Carriage Return or
the LineFeed character.
Larry Linson
Microsoft Access MVP

"JA" <ja*****@kc.rr.comwrote in message
news:CD****************@tornado.rdc-kc.rr.com...
>Larry,

Is there a way to remove Chr$(13) & Chr$(10)?


Oct 9 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.