Forum Discussion
Excel on windows10:summing numbers with "-" sign following the positive part: 14.6-,0.1-, 2.70, 9.3-
Better if you provide sample file with the same. From the post it's not clear do you have numbers formatted such way; or texts which looks like a numbers; or mix of texts and numbers.
Thanks Sergei for having answered so quickly my mail
I send you a small extract from a pdf file I get from an on-line food shop.
It is a list of the food products I bought , and their prices. For some product a discount is made,
and this appears as a number followed by a "-" .
Try to look in this extract from the list :
(the text is in Hebrew, not relevant to my problem !)
What is of interest, in each row, is only the first decimal number (let's say,"first column" )
8.50
3.60-
18.00
41.90
9.00-
etc.
I have to sum up these numbers, with their signs, in order to get their total .
To say the truth, even importing these numbers in Excel from a pdf file is a problem by itself , and I don't know if and how could be solved. I copied the text from pdf and pasted it in Excel but it doesn't look very nice...
Sergei, I hope you'll be able to solve my problem for me.
But, in any case, I thank you a lot for your time !
Jack
Jack Cohen,PhD
Tel Aviv,Israel
-------------------------------------------------------------------------------------
---- ---- 0 1 çé â100 øååãðì ÷æç øåçù äô÷ 419909
8.50 8.50 1 0 çé íøâ 100 øååãðì øåçù äô÷ 419107
3.60- 3.60- 1.00 íøâ 100 øååãðì äô÷ 4.90 éùéà.ôå÷ 2694132 :òöáî
18.00 9.00 2 2 çé 420äéðáâò.ø äéåôà úéòåòù 4386825
41.90 41.90 1 1 çé ìî750 äìåòî úéúë úéæ ïîù 8846219
9.00- 9.00- 1.00 äìåòî úéúë úéæ ïîù 29.90 éùéà.ôå÷ 2694092 :òöáî
3.00- 3.00- 1.00 ìî750 äìåòî úéúë úéæ ïîù 38.90 2696878 :òöáî
19.90 19.90 1 1 çé â412ïåîðé÷ íéòáåøî íéðâã 38527591053
2.00- 2.00- 1.00 øâ 355-412 ø÷àåå÷ ø÷åá éðâã 17.90 2709181 :òöáî
---- ---- 0 1 çé '÷ù20 øåäè ìéîåî÷ úèéìç 70177177737
18.40 18.40 1 1 çé íøâ 250 äèéìî øèìéô äô÷ 4002720005736
8.00 8.00 1 1 çé 80äô÷ øëåñ àìì øèøåå.ëåñ 4014400914924
14.90 14.90 1 1 çé 560öðö ïéòìâ.ì äèîì÷ úéæ 7290016370317
19.90 19.90 1 1 çé íøâ375 øéøî ãìå÷åù ñðèéô 7613033548192
.................................Hi Jack,
How to get data from PDF that's separate question, depends on your Excel version. On 365 it shall be available Power Query connector "From PDF file", you may try it.
I simply copied your text into the Excel sheet. Select column with it, on ribbon Data->Text to Columns.
On the second step of the wizard select space as delimiter. You may click Finish now, it shall work with default settings. If first time, better to check if trailing minuses are selected in Advanced
You may do other adjustments if necessary to select more proper type for the rest of the columns rather than General.
Result will be as
- Hello Sergei,
I followed your (very !) detailed instruction and, got the restructured data exactly in the form I needed. So, I was able to use it immediately, to my aims.
Dear Sergei, I greatly appreciated your kind help, quick and efficient !
Thanking you for your time, very truly yours,
Jack
Jack Cohen, PhD
Tel Aviv, Israel
