Get rid of leading spaces, Trim and Clean doesn't work, Code shows 0

[kangnade]

Hi gurus,

I've read many posts on various forum to solve the problems on the leading space(or space-like stuff), but after trying many functions, including Trim(), Trim(Clean()),Code(Left()), and other ways, I still cannot get rid of it...

Originally I pasted these from a word document, and selected paste with only value, but it doesn't work. Then I re-pasted it into a word, then back into Excel, still doesn't work with Trim() or Trim(Clean()).

I also tried if it is 160, but the Code() shows 0.

Millions of thanks for your help and kindness!

If you need a sample of the problem I am encountering, don't hesitate to leave me an email below I can forward to you. I am new here so I cannot post with attachment...

Thanks a lot!

 

[MARK858]

Try the code below on a copy of your workbook, if it doesn't work then upload your workbook to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing then post the link it provides in the thread.

Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set myRng = ActiveSheet.UsedRange
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart

        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                               myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

 

[kangnade]

Hey guys, here is the link of the file:

https://www.dropbox.com/s/xdmyxww13w5qchr/Sample.xlsx?dl=0

 

[MARK858]

Below is also a function you can try that Rick wrote (forgot I had it)...

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
' Function courtesy of R Rothstein
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

Sub TrimAgain()
    Dim myCell As Range
    For Each myCell In ActiveSheet.UsedRange
        myCell = CleanTrim(myCell)
    Next
End Sub

Put both codes in the same module and run The TrimAgain macro

 

[kangnade]

Hey MARK858, thanks for your quick reply, I tried your VBA code, however, it actually removes everything in the sample haha. But I uploaded a sample file so maybe you could try it out if you have time! I really appreciate it!

 

[MARK858]

Deleted because of last post

 

[Rick Rothstein]

Here is another macro that you can try. In the following macro, the trim operation is identical to Excel's worksheet TRIM function; however, the clean is slightly different. It cleans some additional non-printing characters that Excel's CLEAN function does not handle. Those additional characters are delineated here...

Remove spaces and nonprinting characters from text - Support - Office.com

It also converts non-breaking spaces (ASCII 160) to real spaces (ASCII 32) which will then be handled, along with existing spaces, by the trim operation).

[table="width: 500"]
[tr]
	[td]Sub CleanTrim()
  Dim X As Long, Cell As Range, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  For Each Cell In ActiveSheet.UsedRange
    Cell.Value = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(Cell.Value, Chr(CodesToClean(X))) Then Cell.Value = Replace(Cell.Value, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
End Sub[/td]
[/tr]
[/table]

 

[kangnade]

Hey Mark858, it works somehow, and reduced a bit (I guess the Alt+0160), but if you download the file I shared, you can see there is still much space leading in the cell, which is so stubborn that I really have no idea what it is............T.T

 

[kangnade]

Hi Rick, thanks a lot for your post, I tried your VBA codes, and it doesn't seem to work on this one. You got any other ideas?

 

[Rick Rothstein]

Hi Rick, thanks a lot for your post, I tried your VBA codes, and it doesn't seem to work on this one. You got any other ideas?

I was going with your thread title when I posted my code, but I just now took a look at your posted file... is it those foreign language characters that you want to remove? If so, do you know whether they are always located at the beginning or end of the English text (never in the middle of it)?