TRIM function not working

[Manolocs]

I have no idea why this happens, I already followed threads from this forum and use the following formulas and macros but still can not get ride of the spaces in front of the cells
Any help is more than welcome.


=TRIM(A1)
=SUBSTITUTE(A1,CHAR(160),"")
=TRIM(SUBSTITUTE(D1,CHAR(160),CHAR(32)))

Sub RemoveLeadingSpace()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

 

[MarcelBeug]

Possibly the leading characters are not realy spaces.
Did you verify the code for the first character, e.g.: =CODE(A1)

 

[Trebor76]

Maybe they're not spaces? Try this to find out:

1. Copy the following macro into your workbook:

MsgBox Asc(CStr(Left(ActiveCell, 1)))

2. Ensure the cursor is on the cell with the data

3. Run the macro from step 1 and if the result is not 32 the character is not a space. Look down the Dec column of this reference to determine what it is.

HTH

Robert

 

[RoryA]

Clearly they aren't actually spaces then. What does this show:

Sub FirstChar()
msgbox ascw(left$(range("A1").value, 1))
end sub

 

[Manolocs]

Possibly the leading characters are not realy spaces.
Did you verify the code for the first character, e.g.: =CODE(A1)

Hi MarcelBeug,
the result is 32

 

[Manolocs]

Clearly they aren't actually spaces then. What does this show:

Sub FirstChar()
msgbox ascw(left$(range("A1").value, 1))
end sub

Hi, the result is 32

 

[RoryA]

Then it is a space and trim should work. What does this return:

Sub FirstChar()
msgbox ascw(left$(Trim(range("A1").value), 1))
end sub

 

[Trebor76]

Also, what does this return (just make sure the cursor is on the relevant cell to start with):

MsgBox Len(ActiveCell) & vbNewLine & Len(Trim(ActiveCell))

 

[Manolocs]

Then it is a space and trim should work. What does this return:

Sub FirstChar()
msgbox ascw(left$(Trim(range("A1").value), 1))
end sub

Hi RoryA, this returns 84

 

[Manolocs]

Also, what does this return (just make sure the cursor is on the relevant cell to start with):

MsgBox Len(ActiveCell) & vbNewLine & Len(Trim(ActiveCell))

Sorry I don't know how to use this