Link to home
Start Free TrialLog in
Avatar of sam2929
sam2929

asked on

removing spaces from exel file

Hi,
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

Thanks
Avatar of jbizzle979
jbizzle979
Flag of United States of America image

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

http://www.ablebits.com/excel-trim-spaces/index.php
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam2929
sam2929

ASKER

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

Input

Transmit Date (UTC)
Event COD

Output

transmit_date_utc
event_cod

Thanks
Excel does not have functionality to change the case of text strings. It does have a function you can use:

   =LOWER(A1)

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

Kevin
@sam2929

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.

Enjoy!

Dave
SheetToLowerCase-r1.xlsm