Trim function is not working (1 Viewer)

mubi_masti

Registered User.
Local time
Today, 18:55
Joined
Oct 2, 2011
Messages
46
i m tranferring data in bulk form excel

after transfering the name field of the table shows spaces and i am using trim function but not getting result,

a have a lot of techniques but not able to get results

can any one highlight and solve my problem
 

Attachments

  • TESTING.mdb
    660 KB · Views: 187

Alansidman

AWF VIP
Local time
Today, 09:55
Joined
Jul 31, 2008
Messages
1,493
I looked at your query and was confused also. I believe that you have a non printed ASCII character in the first position and Trim sees it as real character. In your case, I put ASC(Std_ID) in a new column and it returned 160 which is idnetified as a non-breaking space (no symbol)

To delete these non printed characters you will have to use a combination of the Len function and the Right function

Something like =Right(Std_ID,Len(Std_ID)-2) as I believe that there are two spaces in the field.
 

mubi_masti

Registered User.
Local time
Today, 18:55
Joined
Oct 2, 2011
Messages
46
thans a lot
i have done this in excel and able to exclude non printing characters but i have no knowledge wchich can be used to know this kind of spaces,

with the help of ASC i am able to what i want to do

thanks a lot
 

gblack

Registered User.
Local time
Today, 14:55
Joined
Sep 18, 2002
Messages
632
I was also running into the same problem when pulling extracts from Excel to MS Access and did find that a leading ASCII value 160 was the problem (as previously posted).

Seems silly that the Trim function doesn't get rid of all leading and trailing spaces. I don't know why I'd ever want to use Trim and not have it get rid of the leading space, even if it was a "Non-breaking space".

That said, I made a little function to use in an update query to get rid of the any spaces (i.e., either ascii 32 or 160). It works well and this issue comes up now and again with me...

Code:
Public Function RemoveSpaces(ByVal strField As String) As String
If InStr(strField, Chr(160)) > 0 Then
    RemoveSpaces = Replace(strField, Chr(160), "")
ElseIf InStr(strField, Chr(32)) > 0 Then
    RemoveSpaces = Replace(strField, Chr(32), "")
Else
    RemoveSpaces = strField
End If
End Function

Once you have this, just throw that into an Update query and it will get rid of any leading spaces. Well actually it will get rid of all spaces... so just be aware of that, if your string is uspposed to have spaces... you might want to take out this part of the function:

ElseIf InStr(strField, Chr(32)) > 0 Then
RemoveSpaces = Replace(strField, Chr(32), "")

Which I only put in because sometimes, when looking at the data, I have deleted the hard return space (i.e., Chr(160)) and replaced it with a space bar space (i.e., Chr(32)). So I figured I'd remove any spaces, since my strings do not have spaces.
 

ClickSystems

New member
Local time
Today, 14:55
Joined
Jan 7, 2024
Messages
5
Hi,

I know this is an old post but I recently came across the same issue and using gblacks code as a base used the following two line solution which trims the text and then the replace removes the Non-breaking spaces.

Code:
Public Function True_Trim(strText As String) As String

    On Error GoTo Error_Handler
    
    strText = Trim(strText)
    strText = Replace(strText, Chr(160), "")

Exit_Function:

    On Error Resume Next
    
    True_Trim = strText

    Exit Function

Error_Handler:
    
    msgbox Err.Description
    
    strText = ""
    
    Resume Exit_Function
    
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:55
Joined
Sep 21, 2011
Messages
14,054
Wouldn't you want the Replace before the Trim? 🤔
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:55
Joined
Sep 21, 2011
Messages
14,054
Well if the hard space is in the front or back of the string, it would not get trimmed?

However on examining your "" a little closer, it seems there is not a space between each character. I initially thought there was.
Also what happens if it happens to be Chr(32)?
 

ClickSystems

New member
Local time
Today, 14:55
Joined
Jan 7, 2024
Messages
5
Well if the hard space is in the front or back of the string, it would not get trimmed?

However on examining your "" a little closer, it seems there is not a space between each character. I initially thought there was.
Also what happens if it happens to be Chr(32)?
The Trim takes care of chr(32)
 

ClickSystems

New member
Local time
Today, 14:55
Joined
Jan 7, 2024
Messages
5
Hi. Welcome to AWF!

What about the possibility of having soft spaces between hard (non-breaking) spaces, like what @Gasman mentioned? Was that possible?
Hi,

The post was about trimming a string so I was only dealing with regular and non-breaking spaces at the beginning or end of a string, but the replace would take care of the non-breaking spaces where ever they appeared in the string, if you wanted to get rid of all spaces in the string then its just two replace statements.

Code:
    strText = Replace(strText, Chr(32), "")
    strText = Replace(strText, Chr(160), "")

or you could put it all in one line

Code:
strText = Replace(Replace(strText, Chr(32), ""), Chr(160), "")

Hope that helps!
 

Josef P.

Well-known member
Local time
Today, 15:55
Joined
Feb 2, 2023
Messages
800
In the name of a function that removes all spaces (32,160,...), I would not use "Trim", which only trims at the beginning and end.

Code:
strText = Replace(strText, Chr(32), "")
strText = Replace(strText, Chr(160), "")
vs.
Code:
strText = Replace(strText, Chr(160), chr(32))
strText = Trim(strText)

.. eg: strText = chr(160) & "abc xyz " => "abcxyz" vs "abc xyz"
 
Last edited:

ebs17

Well-known member
Local time
Today, 15:55
Joined
Feb 7, 2020
Messages
1,883
A small addition to the content:
With the Replace from the regular expressions you can bundle several values to be removed in one statement. You could also make sure that these values are only removed at the beginning or end of the actual expression, i.e. correspond to the meaning of Trim. Removing spaces in the middle of the printout may well be undesirable.
 

ClickSystems

New member
Local time
Today, 14:55
Joined
Jan 7, 2024
Messages
5
In the name of a function that removes all spaces (32,160,...), I would not use "Trim", which only trims at the beginning and end.

Code:
strText = Replace(strText, Chr(32), "")
strText = Replace(strText, Chr(160), "")
vs.
Code:
strText = Replace(strText, Chr(160), chr(32))
strText = Trim(strText)

.. eg: strText = chr(160) & "abc xyz " => "abcxyz" vs "abc xyz"
Hi Josef,

In 30 years, I've never found a use for a non-breaking space whether at the start, in the middle or at the end of a string, so for my use I'm happy to remove them wherever they appear. Others should amend the code to suit their needs.

Thanks 👍
 

Users who are viewing this thread

Top Bottom