# Thread: Remove Spaces to Right of String

1. Registered User
Join Date
Sep 2011
Posts
2

## Unanswered: Remove Spaces to Right of String

I know this question (or something simliar) has been asked and answered. I an Access DB that I have just started working with. THere are records that have different amounts of spaces at the end of a string. I have tried Trim and Replace and neither work. Any additional suggestions?

2. Registered User
Join Date
Oct 2009
Posts
340
I need to ask; how is it that Trim does not work?

I don't mean to be snarky...but it is the function of the Trim method to do exactly what you seek....and so I'm not clear as to why it would not be working.

3. Registered User
Join Date
Sep 2011
Posts
2
That is why I posted, the trim() did not work . What I think I have figured out is that what appear as blank spaces are really characters that are either unrecognizable or invisible to Access (maybe enters). The data set is an excel download from Cognos.

So, back to my original question, any suggestions how to delete data to the right of the string that I can see?

Thanksl

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
consider using the rtrim function

or if they are not spaces then examine the data to find out what symbols they are

..you may need to write a quick and dirty fucntion to analyse the symbols use the ASC function to ding out the ASCII/ANSI symbol number

5. Moderator
Join Date
Mar 2009
Posts
5,442
I would first try to determine what precisely are those invisible characters:
Code:
```For i = 1 To Len(Field1)
Debug.Print Mid(Field1, i, 1), Asc(Mid(Field1, i, 1))
Next I```
If it's always the same character, say Chr(255), the solution is rather simple, though it can be slow:
Code:
`FieldValue = Left(Field1,Instr(Field1, Chr(255)) -1)`
or:
Code:
`FieldValue = Replace(Field1, Chr(255), "")`
The problem would become more complex if Chr(255) is used everywhere in lieu of Chr(32).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•