1. Sign-up to become a member, and most of the ads you see will disappear. It only takes 30 seconds to sign up, so join the discussion today!
    Dismiss Notice

excel question: blank spaces

Discussion in 'Computers' started by Ted Lee, Jan 14, 2004.

  1. Ted Lee

    Ted Lee Lead Actor

    Joined:
    May 8, 2001
    Messages:
    8,390
    Likes Received:
    0
    Trophy Points:
    0
    hi all -

    does anyone know if excel can parse a spreadsheet and remove any blank spaces at the end of any given cell?

    so the cell looks like:

    "text_" (where _ is a blank space)

    when it should look like:

    "text" (with no blank space at the end).

    hope that makes sense...

    [​IMG]
     
  2. Glenn Overholt

    Glenn Overholt Producer

    Joined:
    Mar 24, 1999
    Messages:
    4,203
    Likes Received:
    0
    Trophy Points:
    0
    I think that makes sense. Put your cursor after the last 't', run it for a few spaces and then highlight and hit the delete button.

    Glenn
     
  3. Tim Markley

    Tim Markley Screenwriter

    Joined:
    Jun 12, 1999
    Messages:
    1,279
    Likes Received:
    0
    Trophy Points:
    0
    There's a VBA function called RTrim that will remove trailing spaces. A1=RTrim(A1) As far as I know, there isn't any kind of function that you could use without running some VBA code.
     
  4. Ted Lee

    Ted Lee Lead Actor

    Joined:
    May 8, 2001
    Messages:
    8,390
    Likes Received:
    0
    Trophy Points:
    0
    thx glenn - that much i know! [​IMG] [​IMG]

    the problem is these spreadsheets can be hundreds of cells large -- if not more. i want excel to automatically "search and destroy"....

    vba code? ugh.... [​IMG]
     
  5. Kraig Lang

    Kraig Lang Stunt Coordinator

    Joined:
    May 28, 2000
    Messages:
    200
    Likes Received:
    0
    Trophy Points:
    0
    Do you have spaces in the text itself? If not, you can do a Replace all.

    If you do, the trim formula will work. What you'll need to do is create a second worksheet. In the A1 cell of your new worksheet, use the formula =TRIM(Firstworksheet!A1), where firstworksheet is the name of your original spread sheet, and then copy the formula into the rest of the cells on the second worksheet.

    The formulas should auto populate with the cells in direct reference from the first worksheet.

    Does that make any sense at all? Sorry if I confused you.

    [Edit] whoops, forgot to mention that you'll need to do a copy/paste special/values into a 3rd worksheet to remove all the formulas.
     
  6. Chris Hovanic

    Chris Hovanic Supporting Actor

    Joined:
    Jan 3, 2003
    Messages:
    545
    Likes Received:
    0
    Trophy Points:
    0
  7. Ted Lee

    Ted Lee Lead Actor

    Joined:
    May 8, 2001
    Messages:
    8,390
    Likes Received:
    0
    Trophy Points:
    0
    AWESOME KRAIG!

    that worked perfectly! thanks!
     

Share This Page