Remove the space at the start of cells

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
165
Office Version
  1. 2019
Platform
  1. Windows
I have copied a large amount of data into an excel file and many of the entries have a space in front of the entry. is there a way I can automatically remove that space if it exists before the text in the cells?
The reason i want to do this is because it is affecting the sort features.

thanks for your help.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
So the leading space only exists in SOME of the cells, but not all ?

Try

=TRIM(A1)
 
Upvote 0
I created a macro to remove leading and trailing spaces and to convert data between types. I have this in my personal macro workbook and have it keybound so that I can quickly access it. Select the data you want to trim and then run this macro.


For your purposes just hit 5 when it prompts you.

Code:
Sub trimmer()
'
' trimmer Macro
'
'Created by Brian Hartman (BrianMH)
    On Error GoTo ErrorHandler
    Dim rSel As Range
    Set rSel = Selection
    Dim lngDate As Long
    Dim c As Range
    Dim vCalc As Variant
    vCalc = Application.Calculation
    Dim strV
    Dim intConv As Integer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    intConv = Application.InputBox("1. Date - 2. Currency - 3. Decimal - 4. long" & Chr(13) & _
                                   "5. Don't convert just trim values" & Chr(13) & _
                                   "6. Convert international (yyyymmdd) dates to normal dates" & Chr(13) & _
                                   "7. Double - 8. Date without Time", , , , , , , 1)


    Set rSel = NonEmptyCells(rSel)
    If rSel.Cells.Count > 5000 Then
        If MsgBox("You have selected a large number of cells, this may take some time, do you want to continue?", vbOKCancel) = vbCancel Then
            GoTo exiter
        End If
    End If


    Select Case intConv
        Case 1
            For Each c In rSel
                If c.Value <> "" Then
                    c.Value = CDate(Trim(c.Value))
                    c.NumberFormat = "dd-mmm-yyyy"
                End If
            Next c
        Case 2
            For Each c In rSel
                If c.Value <> "" Then
                    c.Value = CCur(Trim(c.Value))
                End If
            Next c
        Case 3
            For Each c In rSel
                If c.Value <> "" Then
                    c.Value = CDec(Trim(c.Value))
                End If
            Next c
        Case 4
            For Each c In rSel
                If c.Value <> "" Then
                    c.Value = CLng(Trim(c.Value))
                End If
            Next c
        Case 5
            For Each c In rSel


                If Trim(c.Value) = "" Then c.Value = ""
                If c.Value <> "" Then
                    strV = Trim(c.Value)
                    While Asc(Left(strV, 1)) = 127 Or Asc(Left(strV, 1)) = 129 Or Asc(Left(strV, 1)) = 141 Or Asc(Left(strV, 1)) = 143 Or Asc(Left(strV, 1)) = 144 Or Asc(Left(strV, 1)) = 157 Or Asc(Left(strV, 1)) = 160 Or Asc(Left(strV, 1)) = 10 Or Asc(Left(strV, 1)) = 13
                        strV = Right(strV, Len(strV) - 1)
                        If Not strV <> "" Then GoTo skip
                    Wend
                    While Asc(Right(strV, 1)) = 127 Or Asc(Right(strV, 1)) = 129 Or Asc(Right(strV, 1)) = 141 Or Asc(Right(strV, 1)) = 143 Or Asc(Right(strV, 1)) = 144 Or Asc(Right(strV, 1)) = 157 Or Asc(Right(strV, 1)) = 160 Or Asc(Right(strV, 1)) = 10 Or Asc(Right(strV, 1)) = 13
                        strV = Left(strV, Len(strV) - 1)
                        If Not strV <> "" Then GoTo skip
                    Wend
skip:
                    c.Value = strV
                End If
            Next c
        Case 6
            '20110131'
            For Each c In rSel
                c.NumberFormat = "General"
                If c.Value <> "" Then
                    c.Value = DateValue(Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" & Left(c.Value, 4))
                End If
                c.NumberFormat = "dd-mmm-yyyy"
            Next c
        Case 7
            For Each c In rSel
                If c.Value <> "" Then
                    c.Value = CDbl(Trim(c.Value))
                End If
            Next c
        Case 8
            For Each c In rSel
                If c.Value <> "" Then
                    lngDate = Int(Trim(c.Value2))
                    c.Value = CDate(lngDate)
                    c.NumberFormat = "dd-mmm-yyyy"
                End If
            Next c
        Case False
            MsgBox ("you did not select a conversion type")
    End Select


exiter:
    Application.Calculation = vCalc
    Application.ScreenUpdating = True
    Exit Sub


ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    GoTo exiter


End Sub


Function NonEmptyCells(TestRange As Range) As Range
    Dim r1 As Range
    Dim r2 As Range
    If Not TestRange.Cells.Count > 1 Then
        Set NonEmptyCells = TestRange
        Exit Function
    End If
    On Error Resume Next
    Set r1 = TestRange.SpecialCells(xlCellTypeFormulas)
    Set r2 = TestRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If r1 Is Nothing And Not r2 Is Nothing Then
        Set NonEmptyCells = r2
    ElseIf r2 Is Nothing And Not r1 Is Nothing Then
        Set NonEmptyCells = r1
    ElseIf r2 Is Nothing And r1 Is Nothing Then
        Set NonEmptyCells = TestRange.Cells(1, 1)
    Else
        Set NonEmptyCells = Union(r1, r2)
    End If


End Function
 
Upvote 0
thanks so much Brian. I have never used Macros in Excel before but I will give it a shot.
 
Upvote 0
Solved. Thanks so much Brian.
I swear this is the best website on the internet.
 
Upvote 0
Glad to help.

If you do quite a bit of data importing that macro will probably be useful for other things too. I find often when importing data into excel it will see dates as text (usually due to extra spaces) and this will convert those cells into dates if you hit 1. Hit 8 and that will convert dates with the time into dates without times. It's handy especially when you plan on importing data from excel into a database.
 
Upvote 0
Hi Brian,
Is there any way to use this to extract the phone number at the very end of this long string from the rest of it? I have 1800 lines like this in a sheet.
<a href="#" *******="_kmq.push(['record', 'Contacted Listing', {type: 'Vendor', id: 16693, name: "Anderson Studios Photography & Video", path: '/ann-arbor/anderson-studios-photography-video', destination: 'phone'}]); $(this).replaceWith('810-229-4971

<tbody>
</tbody>
 
Upvote 0
This wouldn't do that. Also I can't see any data in the box. Try posting a sample of the end of the string where the phone number is.
 
Upvote 0
Hi Brian,
Sorry about that, i figured out an easy way to do it with right trim.

thanks for the help.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top