Excel 97 Editing & removal of leading spaces

    Hello,


    I'm using Excel 97 (at home and at work), and need to know how to simply remove leading spaces from columns of text imported from other sources, such as Word.


    The TRIM function only appears to work if you know what the following text is. As I'm dealing in 1,000s of given names at a time, with only so many duplicates, this is not practical.


    Is there any way to do this, or to combine the TRIM and the LEFT function, so it only removes the 1st character on the left of the column? (And the RIGHT function to remove trailing spaces?)


    I've dealt with it for a long time now by dumping the data into Word, converting the resulting table into text, replacing the paragraph mark &" " with a paragraph only, converting the text back into a table, and dumping it back into Excel.


    This works fine, takes only a few steps and is easy to do. It is just very resource hungry when the list is long (most of mine are over 2,000 names long), and my time could be better spent massaging the data I've acquired!


    Looking forward to learning a much more efficient way of doing this, and any help/suggestions are very welcome,


    Cheers,


    H in Melbourne, Vic :)

    H,


    TRIM removes all spaces (leading & trailing) except those between words. It should work fine for your needs.


    In a blank column enter the formula


    =TRIM(A1)


    where A1 contains the text you want to trim. Then drag this formula down the column until you reach the end of your data.
    Next select all this new data and copy. Then, over the top of the original data (in col A), Paste Special - Values. You can now delete the formulas.


    Hope that Helps
    Weasel
    Also in Melbourne

    This VB loop takes about 1 second for 2000 values


    [vba]Sub trimmer()
    Dim rngMydata As Range
    Dim rCell As Range


    Set rngMydata = Sheets(1).[A1:A2000]
    For Each rCell In rngMydata.Cells
    rCell.Value = Trim(rCell.Value)
    Next rCell


    End Sub
    [/vba]


    Hope it helps,


    Will

    Thanks Pesky Weasel,


    Quick response is very much appreciated!


    I did try the =TRIM(CellRef) and drag method that you suggested, prior to posting my question, but for some reason it didn't do the job; the spaces sat there, jeering at me.


    I'll give it another go, but is there anything in Excel's formatting of cells that might prevent it from working?


    Cheers,


    H. :)

    When data come from other sources, often what look like spaces are really other nonprinting characters. In addittion to Trim try using Clean, which removes the non-printing characters. In Will's code it would be
    [vba]
    Sub trimmer()
    Dim rngMydata As Range
    Dim rCell As Range

    Set rngMydata = Sheets(1).[A1:A2000]
    For Each rCell In rngMydata.Cells
    rCell.Value = Trim(Application.Clean(rCell.Value))
    Next rCell

    End Sub
    [/vba]

    There are actually 2 different kinds of printable blank spaces. I came across something similar to what you describe while trying to process data that came from SAP.


    Char 32 = Space
    Char 160 = No Break Space


    Trim works fine for stripping character 32. However, it does NOT remove the printable 160.


    So what is this No Break Space you may ask? Turns out that in some languages, there are words and parts of speach that are seperated by a space but intended to be analyzed as a single word. Screws up the spell/grammar checkers if you use a standard space. So, a "No Break Space" is used to let the computer know it's to be treated as a single word.


    Turns out the Germans who developed SAP thought it would be a good idea to use no break spaces in their data dumps to text files.


    This is the code I developed to convert the no breaks to standard spaces and then run the trim feature.


    [vba]Sub TrimIt()
    On Error Resume Next
    Application.Calculation = xlCalculationManual
    Set Fn = Application.WorksheetFunction
    For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
    cell.Value = Fn.Substitute(cell.Value, Chr(160), Chr(32))
    cell.Value = trim(cell.Value)
    Next
    Application.Calculation = xlCalculationAutomatic
    End Sub[/vba]

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

    Thanks for all the help and suggestions everyone has provided. Curiously enough, on my computer at work, the leading space thing is a problem (still), but the =TRIM(A1) function works fine on my computer at home!


    I've checked the formatting of the column I'm using for the TRIM function at work, and it shows "General". I fiddled around turing it into text, and all that achieved was showing the formula, not the result! So, back to General I went.


    I've come to the conclusion that I may be best off dumping the data into Word, which does recognise leading and trailing spaces as just that, then pasting it into Excel, rather than straight into Excel.


    For those of you suggesting code, I'm afraid I don't use Visual Basic (yet), so if you can tell me how to use the code in Excel (assuming that is possible?), I will be pleased to learn.


    Cheers,


    H. :)


    You need to place the code in a standard workbook code module.


    First you need to open the Visual Basic Editor (Hit the ALT+F11 keys)


    You should have a list of Projects displayed (usually in a small window entitled Project - VBA Project ) One of these will be the project for your relevant workbook. (If it is not there, press CTRL+R and it will be displayed)


    Select the project for your workbook.


    From the menu, select Insert>Module


    Paste the code into the new code pane that will have been inserted... by default this will be called Module 1.


    You can then place a button on your sheet and assign the macro that you have just pasted into the Module to it.


    i.e., for my code, the macro name will be trimmer as then name of each macro is defined by the Sub... as in


    [vba]Sub trimmer()
    Dim rngMydata As Range
    Dim rCell As Range

    Set Fn = Application.WorksheetFunction
    Set rngMydata = Sheets(1).[A1:A2000]
    For Each rCell In rngMydata.Cells
    rCell.Value = Fn.Substitute(rCell.Value, Chr(160), Chr(32))
    rCell.Value = Trim(Application.Clean(rCell.Value))
    Next rCell

    End Sub[/vba]


    You'd just need to change this bit


    [vba]Set rngMydata = Sheets(1).[A1:A2000][/vba]


    to whatever your range is with your data..


    Hope this helps,


    Will

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login