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.

    Love 1

    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!

Register Yourself Login