HOW TO DELETE THE SPACE IN THE CELL

excel_beginner_lx

Board Regular
Joined
Aug 6, 2003
Messages
143
I HAVE A SPREADSHEET IN WHICH SOME CELLS START WITH SPACE INTEAD OF THE WORD ITSELF, IE. ABOUT IS LIKE _ _ _ ABOUT. THERE ARE 3 SPACES IN FRONT.

iS THERE A QUICK WAY TO GET RID OF IT. I HAVE A LOT LIKE THIS AND THEY HAVE DIFFERENT NUMBER OF SPACES.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
excel_beginner_lx said:
I HAVE A SPREADSHEET IN WHICH SOME CELLS START WITH SPACE INTEAD OF THE WORD ITSELF, IE. ABOUT IS LIKE _ _ _ ABOUT. THERE ARE 3 SPACES IN FRONT.

iS THERE A QUICK WAY TO GET RID OF IT. I HAVE A LOT LIKE THIS AND THEY HAVE DIFFERENT NUMBER OF SPACES.

There's a Trim All macro floating around the board. Also ASAP Utilities at

www.asap-utilities.com

has a function to delete leading and trailing spaces
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Dave McRitchie's Trimall will do this in a heartbeat:-

Code:
Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
                      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next   'in case no text cells in selection
    For Each cell In Intersect(Selection, _
                               Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
and a slight amendment to do all sheets if you want
Code:
Sub a_TrimALLSheets()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    For x = 1 To ActiveWorkbook.Worksheets.Count
        Worksheets(x).Activate
        ActiveSheet.UsedRange.Select
        Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
                          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        'Trim in Excel removes extra internal spaces, VBA does not
        On Error Resume Next   'in case no text cells in selection
        For Each cell In Intersect(Selection, _
                                   Selection.SpecialCells(xlConstants, xlTextValues))
            cell.Value = Application.Trim(cell.Value)
        Next cell
        On Error GoTo 0
        Range("A1").Select
    Next x
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
In case a solution without VBA is desirable, there's also the formula TRIM. For example, if you have data in column A:
1) insert the formula "=TRIM(A1)" in B1 and copy down,
2) copy column B and paste special -> values in Column A
3)Remove formulas
 

excel_beginner_lx

Board Regular
Joined
Aug 6, 2003
Messages
143

ADVERTISEMENT

thank you. this is the most value place for my work.
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Just a thought, but do you know how to put the macro in place and use it? Just in case not then the following may help:-

Code:
Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane.  Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to
expand it.  Within that you should see the following:-

VBAProject(Your_Filename)
   Microsoft Excel Objects
        Sheet1(Sheet1)
        Sheet2(Sheet2)
        Sheet3(Sheet3)
        ThisWorkbook

If you have named your sheets then those names will appear in the brackets above as opposed to what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose 'Insert Module' and it will now look like this

VBAProject(Your_Filename)
   Microsoft Excel Objects
        Sheet1(Sheet1)
        Sheet2(Sheet2)
        Sheet3(Sheet3)
        ThisWorkbook
   Modules
        Module1

Double click the Module1 bit and then paste in the macro
code you want to use starting at the Sub xxx() bit and finishing at the End Sub() bit.


Then hit File / Close and return to Microsoft Excel and save the file.  Now just do Tools / Macro / Macros / xxx


If you then want to get rid of the macro, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane.  Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it.  Within that you should see the following:-

VBAProject(Your_Filename)
   Microsoft Excel Objects
        Sheet1(Sheet1)
        Sheet2(Sheet2)
        Sheet3(Sheet3)
        etc..........................
        ThisWorkbook
   Modules
        Module1

Right click on the Module1 and select remove.  When prompted with a question re exporting, just hit no.  Then hit File / Close and return to Microsoft Excel and save the file.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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