Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Thanked 0 Times in 0 Posts

    Search for Initial Space (2000)

    I Inherited a spreadsheet with thousands of entries. I need to clean up the data which includes removing an intial spaces that appear randomly throughout the sheet in various columns.

    For example, I have a column with text entries. Many have a letter as the first character, while there are many that have a space then a letter. (I have the same problem with numerical columns as well.)

    Is there a search/replace I can do to find those initial space cells and remove that pesky space (without touching other spaces within the cell)?

    Thank you for any advice you can provide!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Search for Initial Space (2000)

    This macro might do the trick:

    <pre>Option Explicit

    Sub RemoveExcessSpaces()
    Dim oCell As Range
    Dim sVal As String
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, xlTextValues)
    sVal = oCell.Value
    sVal = Trim(sVal)
    oCell.Value = sVal
    End Sub

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Search for Initial Space (2000)

    Say that you have text and number values in column A.
    You can put the following formula in B1 (or in another column)


    and fill down as far as needed. You can then copy the cells in column B, and use Edit | Paste Special..., Values in column A to replace the old values with the new ones.

    You can also run this macro:

    Sub RepairCells()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If oCell.HasFormula Then
    ' nothing
    ElseIf Trim(oCell) = "" Then
    oCell = ""
    ElseIf IsDate(oCell) Then
    oCell = CDate(oCell)
    ElseIf IsNumeric(oCell) Then
    oCell = CDbl(oCell)
    oCell = Trim(oCell)
    End If
    Next oCell
    End Sub

    Make sure you have a backup copy before trying this, in case things get messed up!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts