Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    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!
    Mary

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    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
    Next
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

    =IF(ISERROR(VALUE(A1)),TRIM(A1),VALUE(A1))

    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)
    Else
    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
  •