Log In
Register
Home
February 2, 2013
Offline
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
July 16, 2010
Offline
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
February 2, 2013
Offline
Hi Mynda
File Attached
Regards/Saliha
July 16, 2010
Offline
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
February 2, 2013
Offline
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
July 16, 2010
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
You can also try
=SUBSTITUTE(D5,CHAR(160),"")+0
February 2, 2013
Offline
Hi Mynda
sorry i have wrongly given office version, please see the below correct details
regards/Saliha
July 16, 2010
Offline
@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.
1 Guest(s)
