Convert cell value to number with 2 decimal places or blank without special characters

  • Hello everyone, I have this formula in a cell which displays the result in General format. I would appreciate it if anyone can help me to edit the formula to get the result in a number format with 2 decimal places and if the result is blank I want the cell completely blank without any special characters in it. The formula is

    Code
    =IF(VLOOKUP($A2,Sheet1!$A:$AZ,16,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,16,0),IF(VLOOKUP($A2,Sheet1!$A:$AZ,32,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,32,0),IF(VLOOKUP($A2,Sheet1!$A:$AZ,38,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,38,0),"")))
  • Format the cell(s) that contain the formula(e)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • But the result is in this format.

    2,01,33,776.81

    and therefore I am not able to format the column

  • In the next sheet, when I try to add / subract the amounts in a different column, it shows as a value error, mainly becuase of the blank cells which have special characters in it. I even tried Ctrl G...... to select the blank cells but due to the special characters I am not able to select the blank cells and delete them.

  • That looks like an Indian number format, try changing the Locale setting to US

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • All my files with amounts are in Indian format. I don't understand how it will help if I change to US format.

  • Are you sure it is not being formatted as Text or is a number stored as Text? That is the only reason Excel would not be able to perform calculations using it.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox. The formula has taken the value from another sheet in the format in that sheet. My question is how do I edit the formula and get the result value in number format with 2 decimal places and if blank, it should not display any special characters in the cell.

  • KjBox. Are you able to edit the formula accordingly. Something like NumberFormat = "0.00" within the formula

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!