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
    Topic Resolution: Resolved
    Viewing 6 reply threads
    • 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
        Guest

        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.
    Viewing 6 reply threads

    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.