#Value! error

[AthmanArumugam]

Hi,

I am getting the #Value! error when I subtract D3 from C3. I know that the problem is in the formatting of the value in column C. I tried as much as formatting as possible.
Can you let me know what needs to be done in order to overcome the #Value! error.

 

[Chihiro]

You can use helper column to trim trailing spaces.
=Trim(c3)

Then copy the column and paste to Note Pad.
Copy the values and paste them back to Excel.

 

[vletm]

C- column change to 'general'-format after that write numbers with correct way!
Set wanted formats like in that file.
You have 'numbers like '6,000.00' '6.000,00' ...

 

[John Jairo V]

Hi @AthmanArumugam.

You have a problem with the char(160), generated by web copy - paste (probably). Then, you can use a helper column to get the correct number. Blessings!

 

[AthmanArumugam]

Hi All - Thanks for your response.
John - Yes you are right I copied from a word document and pasted in excel.
It is hard for me to understand your formula. Are there any easier ways of getting it as a number easy to compute.

 

[Khalid NGO]

Hi to all,
Another way is to use Find & Replace:

Select range C3:C9
Press Ctrl+H
Find what: .??*
Leave blank Replace with field
Replace all

Regards,

 

[Khalid NGO]

Are there any easier ways of getting it as a number easy to compute.

Since you are getting single CHAR(160) at end, you can use Find & Replace I shared above or the following simple formula:
=D3-IF(ISTEXT(C3),LEFT(C3,LEN(C3)-1),C3)

Regards,