removing spaces from exel file

Posted on 2011-04-29
Last Modified: 2012-05-11
I want to remove the space from my exel spread sheet ,i have 10 rows
say row 1 id is in row12345 i want to delete 2345 as it is occuping just row 1
any quick way to do it as i have like 100 rows in there.
i am in 2003

Question by:sam2929
    LVL 5

    Expert Comment

    You can use this freeware to remove sapces from Excel documents. I haved used it and it works great!
    LVL 81

    Accepted Solution

    If you are trying to remove blank rows or rows with a blank cells you can sort your data and any blank rows are sorted to the bottom of the list.

    If you are trying to remove duplicates then the steps below illustrate how to filter a table's records so that only unique records are visible, where uniqueness is defined by one or more contiguous key columns. Additional steps are provided to remove the duplicate records from the table permanently. These steps are for Excel 2003 and earlier.

    To create a list of unique records based on one or more key columns, first sort the table so that the desired unique records appear first in each same key sequence. If more than one column is to be used to define each record's uniqueness but they are not adjacent to each other, move them such that they are in consecutive order. Follow these steps to hide the duplicate records.

    Select the key columns including the headers and choose the menu command Data->Filter->Advanced Filter.

    Check "Unique records only" and click OK.

    The table can now be viewed or copied to another location. To remove the duplicates records from the table permanently, do the following steps.

    Choose the menu command Edit->Office Clipboard to display the Office Clipboard.

    Select the entire table excluding the header row and press CTRL+C to copy them to the clipboard.

    Choose the menu command Data-Filter->Show All.

    Select the entire table excluding the header row and press DELETE.

    In the Office Clipboard click the dropdown next to the top entry and select Paste.

    The resulting table is just the filtered records.


    Author Comment

    How can i convert them to lower cases there are like 50 records


    Transmit Date (UTC)
    Event COD



    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    Excel does not have functionality to change the case of text strings. It does have a function you can use:


    This will put the lower case version of the text in A1 into the cell containing the above formula.

    LVL 41

    Expert Comment


    Put this in a VBA Project Explorer (ALT-F11) code module (Insert module) and run the macro ConvertAllLowerCase() to do deal with case conversion for whatever the active worksheet is:
    Option Explicit
    Sub ConvertAllLowerCase()
    Dim myString As Range
        For Each myString In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues) 'find all text string values
            myString.Value = LCase(myString.Value)
        Next myString
    End Sub

    Open in new window

    See attached for example.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now