Trim function is not working

[mubi_masti]

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

 

[Alansidman]

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]

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]

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...

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]

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.

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]

Wouldn't you want the Replace before the Trim?

 

[ClickSystems]

Wouldn't you want the Replace before the Trim?

What benefit would this give?

 

[Gasman]

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]

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)

 

[theDBguy]

The Trim takes care of chr(32)

Hi. Welcome to AWF!

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

 

[ClickSystems]

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.

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

or you could put it all in one line

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

Hope that helps!

 

[Josef P.]

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.

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

vs.

strText = Replace(strText, Chr(160), chr(32))
strText = Trim(strText)

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

 

[ebs17]

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]

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.

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

vs.

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