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
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
Hi Mynda
File Attached
Regards/Saliha
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
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
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
You can also try
=SUBSTITUTE(D5,CHAR(160),"")+0
Hi Mynda
sorry i have wrongly given office version, please see the below correct details
regards/Saliha
@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.