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

 

[Trebor76]

Getting late here in Oz - but this seems to work:

Option Explicit
Sub Macro2()

    Dim lngLastRow As Long
    Dim rngMyCell As Range
    Dim i As Integer
    
    lngLastRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("A1:E" & lngLastRow)
        If Len(rngMyCell) > 0 And IsNumeric(rngMyCell) = False And rngMyCell.HasFormula = False Then
            rngMyCell.Replace Chr(160), "", xlPart
            rngMyCell = Trim(rngMyCell)
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[Manolocs]

I really don't know what to say. I don't get the same values from CODE(B1) as you do, and the code I have presented works for me.

You're not using a Mac, are you?

Yes I am. is there a problem? I am running many other macros and VBA with no problem.

 

[RoryA]

There are many weird differences between Mac and PC. Are you using Office 2011 or 2016 (or something even earlier)?

 

[Manolocs]

Yes I am. is there a problem? I am running many other macros and VBA with no problem.

Just swap to a PC it is working
Anyway thanks for your help and sorry for wasting your time

 

[RoryA]

Don't worry - it's always good for me to learn of a new Mac vs PC quirk!

 

[Manolocs]

There are many weird differences between Mac and PC. Are you using Office 2011 or 2016 (or something even earlier)?

I am using Excel 2011. on my Mac.