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.