Sum Column

Topic

New Reply

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

 

Reply | Quote

Replies

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

Reply | Quote

Hi,

ScotchJohn

Formula Value also generates Error.

Please try on sample file.

Thanks

Reply | Quote

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

1 user thanked author for this post.

Sueska

Reply | Quote

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…

Reply | Quote

Hi,

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

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

 

Reply | Quote

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.
https://www.ablebits.com/office-addins-blog/2016/11/16/excel-trim-function/#trim-formula-examples

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.

zeddy

Reply | Quote

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.

Sueska

Reply | Quote

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.

zeddy

Reply | Quote

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

[

Reply | Quote

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.

Sueska

Reply | Quote