News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Sum Column

    Posted on WSzmagic Comment on the AskWoody Lounge

    This topic contains 10 replies, has 6 voices, and was last updated by  zeddy 1 month ago.

    • Author
      Posts
    • #1910842 Reply

      WSzmagic
      AskWoody Lounger

      Hi,

      I have numbers from text file which i want to sum the final total in column A.

      I tried format cell option number with decimal place 2 without results, I tried the trim, clean formula to remove space, tried convert to text but no results.

      Some one please help me to get final total, sample file attached.

      Sample

       

      Attachments:
    • #1911193 Reply

      ScotchJohn
      AskWoody Plus

      Use the Value() formula.  Thus if you have a cell A1 formatted as text that contains, say 150 000, then Value(A1) will give you 150 000 as a number with which you can work.

      Dell E5570 Latitude, Intel Core i5 6440@2.60 GHz, 8.00 GB - Win 10 Pro

    • #1911221 Reply

      WSzmagic
      AskWoody Lounger

      Hi,

      ScotchJohn

      Formula Value also generates Error.Error

      Please try on sample file.

      Thanks

      Attachments:
      • #1911924 Reply

        zeddy
        AskWoody_MVP

        Hi

        The reason you are having a problem is that there is an ‘invisible’ ASCII character 160 appended as the last character to the majority of your column A cell entries (there are a few cell entries that don’t have this appended character, e.g. cells [A10], [A46] and [A54] in your sample.xls file). NOTE: You can’t get rid of this unwanted ASCII character 160 using Excel’s TRIM or CLEAN functions

        You can fix this by putting a formula..
        =IF(CODE(RIGHT(A1,1))=160,LEFT(A1,LEN(A1)-1)*1,A1*1)
        ..into adjacent column B (see attached file) and copying down.
        see attached file.

        Another way would be to use Find-and-Replace to get rid of all the unwanted ASCII character 160 in column A.
        1. Put the cellpointer in cell [A1]
        2. In the formula bar, press [Ctrl][C] to copy the last ‘invisible’ character to the clipboard.
        3. With the cellpointer in cell [A1], press [Ctrl][A] to select the entire region
        4. Press [Ctrl][H] to display the Find-and-Replace dialog
        5. In the Find what: box, press [Ctrl][V] to paste the ‘hidden’ ASCII character 160 you copied in Step 2
        6. Leave the Replace with: box empty
        7. Click the [Replace All] button

        You should now be able to Sum the column OK

        zeddy
        Excel Attack Vector Trappist

        Sample-WSzmagic-zeddy1

        Attachments:
        1 user thanked author for this post.
    • #1911253 Reply

      Rick Corbett
      AskWoody_MVP

      Using your sample file, the following formula sums the sample data you provided:

      =SUM(RC[-1]:R[62]C[-1])

      You can then format the result as a number to 2 decimal places.

      Hope this helps…

    • #1911463 Reply

      WSzmagic
      AskWoody Lounger

      Hi,

      Sorry Sir still not providing the real total only selective numbers are summed up

      =SUM(RC[-1]:R[62]C[-1])

       

    • #1911510 Reply

      Sueska
      AskWoody Plus

      Found a formula to work on your data.

      You have a trailing space (of type code value 160) on the majority of your data for which Trim does not work.

      You can remove each space manually or run the formula below.

      =VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160), ” “))))

      Ran the following for showing what code the trailing space was
      =CODE(RIGHT(A1,1))
      The result was 160.

      Also, examined the dataset in notepad++ and changed Encoding to ANSI. Then I could see many trailing spaces which that were depicted as a capital A’s with circumflex.

      Credit goes to the following website.

      Excel TRIM function – quick way to remove extra spaces

      From the article above. “The TRIM function is designed to remove only the space character, represented by code value 32 in the 7-bit ASCII character set. In the Unicode character set, there is one more space character called the non-breaking space, which is commonly used on web pages as the html character  . The nonbreaking space has a decimal value of 160, and the TRIM function cannot remove it by itself.”

      1 user thanked author for this post.
      • #1911949 Reply

        zeddy
        AskWoody_MVP

        Hi Sueska

        ..saw your reply after I posted my response.
        Nice formula! Shorter than the one I posted.
        So, based on your formula, I would now use..
        =SUBSTITUTE(A1,CHAR(160),””)*1

        zeddy
        Excel Scutwork Automation Engineer

        1 user thanked author for this post.
        • #1912248 Reply

          Sueska
          AskWoody Plus

          Hello @zeddy. Yours revised formula even nicer yet. I should mention for the OP, that if you copy and paste a formula with quotes from woody’s website, replace the quotes with quotes typed in from your keyboard.
          Your explanation of using Find and Replace above very helpful. Often forget that one needs to copy and paste the “invisible” character, just using a space enterred from the keyboard in find and replace won’t work in this case. Thanks.

          1 user thanked author for this post.
          • #1913068 Reply

            zeddy
            AskWoody_MVP

            Hi Sueska

            Thanks for the reminder about changing those pesky double-quote chars when copying from THIS website.

            That char 160 is the “sticky space” character we use to keep things “joined together” when we use Word. It is something that looks like a space, prints like a space, but sticks things together to keep them on one line. For example, we could use a “sticky space” in the middle of John Doe so that Word doesn’t leave John dangling at the end of a line with Doe at the beginning of the next line.
            Because it prints like a space, Excel’s CLEAN function won’t remove it (since that function only removes “non-printing” characters).
            In Word, I think you would use [Ctrl][Shift][space] to insert a “sticky space”, but in Excel, that keyboard shortcut [Ctrl][Shift][space] would select the entire sheet.
            In Excel, [Ctrl][space] selects the entire column wherever your cellpointer is, and [Shift][space] selects the entire row, so it makes sense when you combine the two with [Ctrl][Shift][space] to select the whole worksheet.

            It is tricky to select and copy that “sticky space” character for the Find-and-Replace operation discussed above.
            Another way to enter that character directly into the Find What: box would be to hold the [Alt] key down and then, using the numeric keypad, enter 0160 to put that “sticky space” character in.

            zeddy
            Excel Non-Volatile Manager

            [

    • #1911565 Reply

      anonymous

      If the original numbers came from a text file and, as Sueska says, there are embedded and/or trailing spaces, why not use a text editor to search for any space characters and replace them with nothing. That effectively removes all space characters using a very simple search and replace in a text editor (imo, much simpler than using the trim function in Excel and universally removing any and all spaces in a text file) before doing a copy and paste into Excel. [The text editor I use is NoteTab Light, which has a very useful search and replace capability, but I expect most, if not all, text editors would be able to do the same.]

      1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Sum Column

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.