Trim Help

southcoast

New Member
Joined
Jan 17, 2017
Messages
2
Hi all, I am a new member, don’t know if anyone can help. I am trying to find a “Formula” or VBA, (preferably a formula), to trim a complete"worksheet" in Mac Excel 2016, I am ok on a column, thats not a problem but when I have 12 columns some times 15 columns in a table/worksheet it would be nice to TRIM all in one go instead of 1 column at a time. I look forward to any reply, Regards, Roger.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
Try this:

Code:
Sub Trim_Me()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.Value <> "" Then c.Value = Trim(c.Value)
    Next
End Sub

Works on PC not sure about Mac
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,731
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Don't know about Mac but try...
Code:
Sub SelectUsedRange()
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell.Value)
Next cell
End Sub
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If your range is very large, this may be faster. Select the entire range you want to trim with your mouse when prompted.
Code:
Sub TrimAllCells()
Dim R As Range, v As Variant
Dim i As Long, j As Long, ct As Long
On Error Resume Next
Set R = Application.InputBox("Select the range you want to trim", Type:=8)
On Error GoTo 0
If R Is Nothing Then Exit Sub
v = R.Value
For i = 1 To UBound(v, 1)
       For j = 1 To UBound(v, 2)
              ct = ct + 1
              v(i, j) = WorksheetFunction.Trim(v(i, j))
       Next j
Next i
MsgBox ct & " cells in the range " & R.Address(0, 0) & " have been trimmed"
End Sub
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

southcoast,

Welcome to the MrExcel forum.

I have no experience on a Mac, but, here is a macro solution for you to consider on a PC, that does not do any looping thru all the cells in the used range of a worksheet.

Sample raw data in the active worksheet:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">a   a</td><td style=";">   b    b</td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">     a   a</td><td style=";">b    b</td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">a   a</td><td style=";">   b    b</td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">     a   a</td><td style=";">b    b</td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">a   a</td><td style=";">   b    b</td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">     a   a</td><td style=";">b    b</td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">a   a</td><td style=";">   b    b</td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">     a   a</td><td style=";">b    b</td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">a   a</td><td style=";">   b    b</td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">     a   a</td><td style=";">b    b</td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">     c     c</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c     c</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

And, after the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">a a</td><td style=";">b b</td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c c</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub TrimMeV2()
' hiker95, 01/17/2017, ME986219
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With .Range(.Cells(1, 1), .Cells(lr, lc))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  .Columns(1).Resize(, lc).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the TrimMeV2 macro.
 

southcoast

New Member
Joined
Jan 17, 2017
Messages
2
Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger
 

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

Although it's already answered here is what I use

Code:
Sub Trim()
'
' Removes all leading and trailing spaces
'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveSheet.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
    Selection = Evaluate("index(clean(trim(" & Selection.Address & ")),)")
    Selection.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart, SearchOrder:=xlByRows
    Dim cell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each cell In Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
        cell.Value = Application.WorksheetFunction.Clean(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    End With
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
Glad to see a half dozen different answers here. I will move on and find a posting which no one has provided a answer for.
Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Re: Trim Help
Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger

southcoast,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top