• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

removing spaces from exel file

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

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

zorvek (Kevin Jones)ConsultantCommented:
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.

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


Transmit Date (UTC)
Event COD



zorvek (Kevin Jones)ConsultantCommented:
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.


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.



Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now