Forum

Notifications
Clear all

Total value not display

9 Posts
3 Users
0 Reactions
75 Views
Md Saliha
(@navsal66)
Posts: 111
Estimable Member
Topic starter
 

Hi All

i have a issues in total value, while copying value from email to excel that's data not summing.

i have remove extra space $261.27 then only displaying.

Value
$           261.27
$         5,158.33
$         3,039.69
$         4,637.50
$       10,495.92
$        11,221.19
$        15,865.22
$        11,629.63
$        18,037.19

Total      $261.27

 
Posted : 19/11/2019 3:21 am
(@mynda)
Posts: 4762
Member Admin
 

Hi Saliha,

I can't tell from the data pasted into the forum thread, I'd need to see your Excel file, but I suspect the data has come into Excel as text instead of numbers. Depending on the version of Excel that you have, you can use Text to Columns or Power Query to fix the data.

Mynda

 
Posted : 19/11/2019 4:28 am
Md Saliha
(@navsal66)
Posts: 111
Estimable Member
Topic starter
 

Hi Mynda

File Attached

Regards/Saliha

 
Posted : 19/11/2019 5:06 am
(@mynda)
Posts: 4762
Member Admin
 

Hi Saliha,

Thanks for sharing the file. As expected, the numbers are formatted as text, which is caused by the $ sign with several spaces after it. Since I don't know what version of Excel you have, you can fix it by using Text to Columns. 

1. Select the data > Data tab > Text to Columns

2. Step 1 of the wizard: choose 'Delimited'

3. Step 2: check the box for 'Space'.

4. Step 3: Select the first column and choose 'Do not import column (skip)' > click Finish

Mynda

 
Posted : 19/11/2019 7:07 am
Md Saliha
(@navsal66)
Posts: 111
Estimable Member
Topic starter
 

Hi Mynda

Office 2017 i m using by the way each time i have do this function? there is no any other way.

Regards/Saliha

 
Posted : 19/11/2019 7:47 am
(@mynda)
Posts: 4762
Member Admin
 

Hmm, there's no such version as Office 2017. Can you check again?

A formulaic approach is to use SUBSTITUTE to remove the $ sign and the characters preceding the number e.g.:

=VALUE(SUBSTITUTE(SUBSTITUTE(D5,"$","")," ",""))

Note: the value in the second substitute 'old text' argument is not simply a space, you must copy it from the original text. Select one of the blank spaces in the amount you pasted in from the email and then paste it between the double quotes in the 'old text' argument. 

Mynda

 
Posted : 19/11/2019 6:55 pm
(@sunnykow)
Posts: 1417
Noble Member
 

You can also try

=SUBSTITUTE(D5,CHAR(160),"")+0

 
Posted : 19/11/2019 8:43 pm
Md Saliha
(@navsal66)
Posts: 111
Estimable Member
Topic starter
 

Hi Mynda

sorry i have wrongly given office version, please see the below correct details

office-version..jpg

regards/Saliha

 
Posted : 20/11/2019 3:32 am
(@mynda)
Posts: 4762
Member Admin
 

@SunnyKow, interestingly, CHAR(160) didn't work consistently in my testing, hence my reply. Not sure why.

@Saliha, did you try the formula approach. The version of Office will not matter for the formula.

 
Posted : 20/11/2019 6:47 pm
Share: