Closed Thread
Results 1 to 5 of 5

A better way to get rid of spaces

  1. #1
    The parawon
    Guest

    A better way to get rid of spaces

    I'm looking for a better way to get rid of the spaces directly before and
    after the values in my cells. When I double click the values, I see the
    cursor blinking one space to the right of the data, like this: 'Sales . The
    period is where the cursor sits blinking. Without double clicking the cell,
    it says: Sales No cursor appears on the unactivated cell (obviously). I
    know about the "TRIM" function in order to get rid of before and after
    spaces, but I don't want to have to move the data around in order to do a
    vlookup. I tried using Find & Replace, but that only helps me find values
    that are not formula. Is there a way to replace the spaces on the outside?
    Any push in the right direction would be very appreciated.

  2. #2
    Ken Wright
    Guest

    Re: A better way to get rid of spaces

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ----------------------------------------------------------------------------
    It's easier to beg forgiveness than ask permission :-)
    ----------------------------------------------------------------------------

    "The parawon" <[email protected]> wrote in message
    news:[email protected]...
    > I'm looking for a better way to get rid of the spaces directly before and
    > after the values in my cells. When I double click the values, I see the
    > cursor blinking one space to the right of the data, like this: 'Sales .

    The
    > period is where the cursor sits blinking. Without double clicking the

    cell,
    > it says: Sales No cursor appears on the unactivated cell (obviously).

    I
    > know about the "TRIM" function in order to get rid of before and after
    > spaces, but I don't want to have to move the data around in order to do a
    > vlookup. I tried using Find & Replace, but that only helps me find values
    > that are not formula. Is there a way to replace the spaces on the

    outside?
    > Any push in the right direction would be very appreciated.




  3. #3
    JE McGimpsey
    Guest

    Re: A better way to get rid of spaces

    Take a look here:

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall



    In article <[email protected]>,
    "The parawon" <[email protected]> wrote:

    > I'm looking for a better way to get rid of the spaces directly before and
    > after the values in my cells. When I double click the values, I see the
    > cursor blinking one space to the right of the data, like this: 'Sales . The
    > period is where the cursor sits blinking. Without double clicking the cell,
    > it says: Sales No cursor appears on the unactivated cell (obviously). I
    > know about the "TRIM" function in order to get rid of before and after
    > spaces, but I don't want to have to move the data around in order to do a
    > vlookup. I tried using Find & Replace, but that only helps me find values
    > that are not formula. Is there a way to replace the spaces on the outside?
    > Any push in the right direction would be very appreciated.


  4. #4
    Steve
    Guest

    Re: A better way to get rid of spaces

    Hi

    Copy the code below into a module, highlight your problem cells, press
    Alt/F8 and run the macro:

    Sub trimit()

    For Each c In Selection
    c.Value = Trim(c.Value)
    Next

    End Sub

    Another way is to trim the cells in another row then copy, paste special,
    values over the top or original data. However, this won't work on a formula
    as it will copy the value over the formula.


    "The parawon" <[email protected]> wrote in message
    news:[email protected]...
    > I'm looking for a better way to get rid of the spaces directly before and
    > after the values in my cells. When I double click the values, I see the
    > cursor blinking one space to the right of the data, like this: 'Sales .
    > The
    > period is where the cursor sits blinking. Without double clicking the
    > cell,
    > it says: Sales No cursor appears on the unactivated cell (obviously).
    > I
    > know about the "TRIM" function in order to get rid of before and after
    > spaces, but I don't want to have to move the data around in order to do a
    > vlookup. I tried using Find & Replace, but that only helps me find values
    > that are not formula. Is there a way to replace the spaces on the
    > outside?
    > Any push in the right direction would be very appreciated.




  5. #5
    Ken Wright
    Guest

    Re: A better way to get rid of spaces

    As long as there are no formulas in that data :-)

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ----------------------------------------------------------------------------
    It's easier to beg forgiveness than ask permission :-)
    ----------------------------------------------------------------------------

    "Steve" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    >
    > Copy the code below into a module, highlight your problem cells, press
    > Alt/F8 and run the macro:
    >
    > Sub trimit()
    >
    > For Each c In Selection
    > c.Value = Trim(c.Value)
    > Next
    >
    > End Sub
    >
    > Another way is to trim the cells in another row then copy, paste special,
    > values over the top or original data. However, this won't work on a

    formula
    > as it will copy the value over the formula.
    >
    >
    > "The parawon" <[email protected]> wrote in message
    > news:[email protected]...
    > > I'm looking for a better way to get rid of the spaces directly before

    and
    > > after the values in my cells. When I double click the values, I see the
    > > cursor blinking one space to the right of the data, like this: 'Sales .
    > > The
    > > period is where the cursor sits blinking. Without double clicking the
    > > cell,
    > > it says: Sales No cursor appears on the unactivated cell (obviously).
    > > I
    > > know about the "TRIM" function in order to get rid of before and after
    > > spaces, but I don't want to have to move the data around in order to do

    a
    > > vlookup. I tried using Find & Replace, but that only helps me find

    values
    > > that are not formula. Is there a way to replace the spaces on the
    > > outside?
    > > Any push in the right direction would be very appreciated.

    >
    >




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1