Strange results using trim statement

[jag]

I'm moving text from several old poorly formatted documents into newly
formatted documents, by searching for certain "text markers", copy the data
and pasting into the new document. I'm doing this with ranges. All this is
working perfect.
My problem is that when the text is copied from the old document it's
picking up some weird spaces with the text. I checked the old document and
indeed the spaces are there, some are even bold. So I figured I would just
use the TRIM statement and remove all these. Well when I use the TRIM
statement it only removes 1 space.
The code I'm using for the TRIM is below:

TempText = AppraisalDoc.Range(TextToSearch1.End + 1, TextToSearch2.End -
Len(TextToFind))

Why is it only removing 1 space, not all of them??

TIA

 

[jag]

Oops, sorry, the line should read....

TempText = Trim(AppraisalDoc.Range(TextToSearch1.End + 1, TextToSearch2.End
- Len(TextToFind)))

 

[Jonathan West]

Oops, sorry, the line should read....

TempText = Trim(AppraisalDoc.Range(TextToSearch1.End + 1,
TextToSearch2.End
- Len(TextToFind)))

Its failing presumably because they are "wierd" spaces and not oridinary
ones which Trim is designed to cope with.

You need to find out the character code of the wierd spaces. They could be
nonbreaking spaces, em-spaces or en-spaces. Whatever they are, once you know
the code, you can replace them with ordinary spaces and then use Trim.

With the cursor at one of the spaces in the document, move to the VBA
editor, Press Ctrl-G to open the immediate window, and type the following

? Asc(Selection.Text)

and press Enter.

A number will be printed below. Remember that number. If it is a nonbreaking
space, it will be 160.

Then you can modify your code as follows

TempText = AppraisalDoc.Range(TextToSearch1.End + 1, _
TextToSearch2.End - Len(TextToFind))
TempText = Replace(TempText, Chr$(160), " ")
TempText = Trim$(TempText)

if it is not a nonbreaking space you have, replace 160 with the code of the
character you found.

 

[jag]

Thanks for this info, did as you suggested and the code comes back
as 32, which is a true space, correct???
Even tried it using replace with the code 32 and Trim still only removes 1
space... I can't figure out how to get these extra spaces or whatever they
are
removed..... help...

 

[Jonathan West]

Thanks for this info, did as you suggested and the code comes back
as 32, which is a true space, correct???
Even tried it using replace with the code 32 and Trim still only removes 1
space... I can't figure out how to get these extra spaces or whatever
they
are
removed..... help...

Select the offending text, and run the following short macro

Sub ShowCodes
Dim strText as String
Dim i As long
strText = Selection.Text
For i = 1 to Len(strText)
Debug.Print Asc(Mid$(strText, i, 1));
Next i
End Sub

This will show in the Immediate window the codes for each character in the
text you selected. Trim does remove all leading spaces (code 32) so there
must be some other character in there.

 

[jag]

Runing this code shows 3 spaces in front of the text, and 35 spaces
at the end of the text. All the codes show up as 32...
I'm trying to remove all the extra spaces at the beginning and end of
the text. Trim is the correct function to use isn't it???

 

[Jonathan West]

Runing this code shows 3 spaces in front of the text, and 35 spaces
at the end of the text. All the codes show up as 32...
I'm trying to remove all the extra spaces at the beginning and end of
the text. Trim is the correct function to use isn't it???

Trim is certainly the correct function. OK, so let's take it to the next
step. The following code sample uses the Trim function to remove the spaces
from the string. This time, the "32" codes should be gone from the start and
the end of the list

Sub ShowCodes
Dim strText as String
Dim i As long
strText = Trim$(Selection.Text)
For i = 1 to Len(strText)
Debug.Print Asc(Mid$(strText, i, 1));
Next i
End Sub

If they are gone, then you can be sure that Trim is working correctly. In
which case, we need to see what you are doing with TempText after you have
set it.

 

[jag]

Well this certainly seems to strip the spaces off, all the spaces
seemed to be gone, only text characters were left. So what could
be happening to TempText????

 

[Jonathan West]

Well this certainly seems to strip the spaces off, all the spaces
seemed to be gone, only text characters were left. So what could
be happening to TempText????

What do you do with TempText afterwards?

 

[jag]

I just take TempText and set the value of a bookmark in another
document = TempText. See code below.

Public Sub Copy_Title()

'Grab Title Text & Update Base Document

TextToFind = "TITLE: "

With TextToSearch1.Find
.Text = TextToFind
.MatchCase = True
.Execute
If .Found Then
ResetSearch

TextToFind = "JOB CLASS:"

With TextToSearch2.Find
.Text = TextToFind
.MatchCase = True
.Execute
If .Found Then
TempText = Trim$(AppraisalDoc.Range(TextToSearch1.End
+ 1,_ TextToSearch2.End - Len(TextToFind)))
BaseDoc.Bookmarks("BM_Title").Range.Text = TempText
End If
End With
End If
End With

End Sub