+ Reply to Thread
Results 1 to 11 of 11

TRIM?

  1. #1
    Cthulhu
    Guest

    TRIM?

    I have a very simple spreadsheet with two columns of numbers. All I want to
    do is add the numbers, which I understand I can do with the SUM function.

    My problem is that each number has a blank space at the end of it, so the
    sum function returns a "0" total when adding the columns. I determined I
    could delete the blank space at the end of the numbers and the SUM would work
    fine. However the spreadsheet has almost 200 entries and my fingers are
    tired.

    I cannot seem to get the TRIM function to work, and indenting the cells has
    no impact on the formula working.

    How can I get rid of these pesky spaces?

    Thank you.

  2. #2
    cwee
    Guest

    RE: TRIM?

    simple fix: do a find and replace (edit menu) for space characters and
    replace them with nothingness, let me know if this works for you

    "Cthulhu" wrote:

    > I have a very simple spreadsheet with two columns of numbers. All I want to
    > do is add the numbers, which I understand I can do with the SUM function.
    >
    > My problem is that each number has a blank space at the end of it, so the
    > sum function returns a "0" total when adding the columns. I determined I
    > could delete the blank space at the end of the numbers and the SUM would work
    > fine. However the spreadsheet has almost 200 entries and my fingers are
    > tired.
    >
    > I cannot seem to get the TRIM function to work, and indenting the cells has
    > no impact on the formula working.
    >
    > How can I get rid of these pesky spaces?
    >
    > Thank you.


  3. #3
    JulieD
    Guest

    Re: TRIM?

    Hi

    TRIM should remove the space, however it doesn't necessarily change a
    "text - number" into a real number
    so TRIM your column in an adjacent column, then copy this column and choose
    edit / paste special - values
    now click in a blank cell somewhere (maybe on another worksheet) and copy
    it - then select this column again and choose edit / paste special - ADD

    this should set the numbers back to being numbers.

    Hope this helps
    Cheers
    JulieD

    "Cthulhu" <[email protected]> wrote in message
    news:[email protected]...
    >I have a very simple spreadsheet with two columns of numbers. All I want
    >to
    > do is add the numbers, which I understand I can do with the SUM function.
    >
    > My problem is that each number has a blank space at the end of it, so the
    > sum function returns a "0" total when adding the columns. I determined I
    > could delete the blank space at the end of the numbers and the SUM would
    > work
    > fine. However the spreadsheet has almost 200 entries and my fingers are
    > tired.
    >
    > I cannot seem to get the TRIM function to work, and indenting the cells
    > has
    > no impact on the formula working.
    >
    > How can I get rid of these pesky spaces?
    >
    > Thank you.




  4. #4
    cwee
    Guest

    RE: TRIM?

    ah, yes, you might have to format your cells as numbers instead of as
    "general" which they may be by default. Julie makes a good point

    "cwee" wrote:

    > simple fix: do a find and replace (edit menu) for space characters and
    > replace them with nothingness, let me know if this works for you
    >
    > "Cthulhu" wrote:
    >
    > > I have a very simple spreadsheet with two columns of numbers. All I want to
    > > do is add the numbers, which I understand I can do with the SUM function.
    > >
    > > My problem is that each number has a blank space at the end of it, so the
    > > sum function returns a "0" total when adding the columns. I determined I
    > > could delete the blank space at the end of the numbers and the SUM would work
    > > fine. However the spreadsheet has almost 200 entries and my fingers are
    > > tired.
    > >
    > > I cannot seem to get the TRIM function to work, and indenting the cells has
    > > no impact on the formula working.
    > >
    > > How can I get rid of these pesky spaces?
    > >
    > > Thank you.


  5. #5
    SauveC
    Guest

    Re: TRIM?

    Take JulieD's suggestion one step further. In a blank column next to your
    list of numbers enter the formula =VALUE(TRIM(number)) then copy and paste
    values over your original column and delete the formula column. This will
    remove the extra space and convert it to a number all at the same time.

    "JulieD" wrote:

    > Hi
    >
    > TRIM should remove the space, however it doesn't necessarily change a
    > "text - number" into a real number
    > so TRIM your column in an adjacent column, then copy this column and choose
    > edit / paste special - values
    > now click in a blank cell somewhere (maybe on another worksheet) and copy
    > it - then select this column again and choose edit / paste special - ADD
    >
    > this should set the numbers back to being numbers.
    >
    > Hope this helps
    > Cheers
    > JulieD
    >
    > "Cthulhu" <[email protected]> wrote in message
    > news:[email protected]...
    > >I have a very simple spreadsheet with two columns of numbers. All I want
    > >to
    > > do is add the numbers, which I understand I can do with the SUM function.
    > >
    > > My problem is that each number has a blank space at the end of it, so the
    > > sum function returns a "0" total when adding the columns. I determined I
    > > could delete the blank space at the end of the numbers and the SUM would
    > > work
    > > fine. However the spreadsheet has almost 200 entries and my fingers are
    > > tired.
    > >
    > > I cannot seem to get the TRIM function to work, and indenting the cells
    > > has
    > > no impact on the formula working.
    > >
    > > How can I get rid of these pesky spaces?
    > >
    > > Thank you.

    >
    >
    >


  6. #6
    Cthulhu
    Guest

    Re: TRIM?

    Thank you all for your suggestions.

    SauveC--

    I did try your suggestion combined with Julie's. It worked perfectly for
    the first cell. When I tried to drag the fromula down the rest of the
    worksheet, however, I get a "#VALUE" error. Now what?


  7. #7
    SauveC
    Guest

    Re: TRIM?

    I can't duplicate your error. You should have been able to copy it down
    without any problem if it worked for the first entry. Another function that
    can be used is CLEAN in place of TRIM in the formula. See if that makes any
    difference.

    "Cthulhu" wrote:

    > Thank you all for your suggestions.
    >
    > SauveC--
    >
    > I did try your suggestion combined with Julie's. It worked perfectly for
    > the first cell. When I tried to drag the fromula down the rest of the
    > worksheet, however, I get a "#VALUE" error. Now what?
    >


  8. #8
    Ken Wright
    Guest

    Re: TRIM?

    Select all your data and do Edit / Replace, replacing space with noithing,
    as already suggested. Then type a 1 in a cell somewhere, copy the cell,
    select all your data and do Edit / Paste Special / values & multiply, or
    copy an empty cell and select all and do Edit / Paste Special / values and
    Add.

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

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

    "Cthulhu" <[email protected]> wrote in message
    news:[email protected]...
    > Thank you all for your suggestions.
    >
    > SauveC--
    >
    > I did try your suggestion combined with Julie's. It worked perfectly for
    > the first cell. When I tried to drag the fromula down the rest of the
    > worksheet, however, I get a "#VALUE" error. Now what?
    >




  9. #9
    Cthulhu
    Guest

    Re: TRIM?

    Wow, I seem to be so close. I don't understand why the formula won't carry
    down the column without an error.

    I also do not know how to replace space with nothing. What am I typing for
    "FInd what" and "Replace with"?

    "Ken Wright" wrote:

    > Select all your data and do Edit / Replace, replacing space with noithing,
    > as already suggested. Then type a 1 in a cell somewhere, copy the cell,
    > select all your data and do Edit / Paste Special / values & multiply, or
    > copy an empty cell and select all and do Edit / Paste Special / values and
    > Add.
    >
    > --
    > Regards
    > Ken....................... Microsoft MVP - Excel
    > Sys Spec - Win XP Pro / XL 97/00/02/03
    >
    > ----------------------------------------------------------------------------
    > It's easier to beg forgiveness than ask permission :-)
    > ----------------------------------------------------------------------------
    >
    > "Cthulhu" <[email protected]> wrote in message
    > news:[email protected]...
    > > Thank you all for your suggestions.
    > >
    > > SauveC--
    > >
    > > I did try your suggestion combined with Julie's. It worked perfectly for
    > > the first cell. When I tried to drag the fromula down the rest of the
    > > worksheet, however, I get a "#VALUE" error. Now what?
    > >

    >
    >
    >


  10. #10
    Ken Wright
    Guest

    Re: TRIM?

    You don't need any formulas with the instructions I gave you

    When you get the Find/Replace box up, put the cursor in the Find What bit
    and hit the space bar once. Leave the Replace with empty and hit OK.

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

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

    "Cthulhu" <[email protected]> wrote in message
    news:[email protected]...
    > Wow, I seem to be so close. I don't understand why the formula won't

    carry
    > down the column without an error.
    >
    > I also do not know how to replace space with nothing. What am I typing

    for
    > "FInd what" and "Replace with"?
    >
    > "Ken Wright" wrote:
    >
    > > Select all your data and do Edit / Replace, replacing space with

    noithing,
    > > as already suggested. Then type a 1 in a cell somewhere, copy the cell,
    > > select all your data and do Edit / Paste Special / values & multiply, or
    > > copy an empty cell and select all and do Edit / Paste Special / values

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

    >
    > --------------------------------------------------------------------------

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

    >
    > --------------------------------------------------------------------------

    --
    > >
    > > "Cthulhu" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Thank you all for your suggestions.
    > > >
    > > > SauveC--
    > > >
    > > > I did try your suggestion combined with Julie's. It worked perfectly

    for
    > > > the first cell. When I tried to drag the fromula down the rest of the
    > > > worksheet, however, I get a "#VALUE" error. Now what?
    > > >

    > >
    > >
    > >




  11. #11
    Gord Dibben
    Guest

    Re: TRIM?

    What: enter a space using spacebar.

    With: do not enter anything

    Perhaps TRIM won't work because you have an HTML non-breaking space in the
    cell.

    To get rid of that....edit>replace

    What: hit ALT + 0160 using the numpad

    With: do not enter anything


    Gord Dibben Excel MVP

    On Thu, 10 Feb 2005 10:17:05 -0800, "Cthulhu"
    <[email protected]> wrote:

    >Wow, I seem to be so close. I don't understand why the formula won't carry
    >down the column without an error.
    >
    >I also do not know how to replace space with nothing. What am I typing for
    >"FInd what" and "Replace with"?
    >
    >"Ken Wright" wrote:
    >
    >> Select all your data and do Edit / Replace, replacing space with noithing,
    >> as already suggested. Then type a 1 in a cell somewhere, copy the cell,
    >> select all your data and do Edit / Paste Special / values & multiply, or
    >> copy an empty cell and select all and do Edit / Paste Special / values and
    >> Add.
    >>
    >> --
    >> Regards
    >> Ken....................... Microsoft MVP - Excel
    >> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>
    >> ----------------------------------------------------------------------------
    >> It's easier to beg forgiveness than ask permission :-)
    >> ----------------------------------------------------------------------------
    >>
    >> "Cthulhu" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Thank you all for your suggestions.
    >> >
    >> > SauveC--
    >> >
    >> > I did try your suggestion combined with Julie's. It worked perfectly for
    >> > the first cell. When I tried to drag the fromula down the rest of the
    >> > worksheet, however, I get a "#VALUE" error. Now what?
    >> >

    >>
    >>
    >>



+ Reply to 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