Ingore Inch Symbol in Numbers

    i created an IF function that chooses between a set number of values the problem is that source values are writen in inches(with the inch symbol "")my problem is that excel is no longer reognizing the values as numbers i cant control the way the values because they are linked to an outside CAD program(hence why they apper with the inch symbol)


    in short this what appears in the reference cell-->5.000" but in formula i want excel to ignore the " symbol and start seeing the number 5 and not a text!


    thanks in adavance:)

    Hi RBX,


    In case you want to leave your IF formulas as they are, the simplest thing to do is to run a Find/Replace on the cells containing the data, and remove all the quote marks.


    1. Select the cells containing the data
    2. Press Ctrl + H (or Edit > Replace)
    3. For "Find What:", enter Quote Mark ( " )
    4. Click the "Replace All" Button


    This will remove the quote marks from all the selected cells, and hopefully your IF function should start working OK.


    In case you want to retain the inch symbol in your data, you can apply Custom Formatting after running the above-mentioned replace command.


    1. Select the cells containing the data
    2. Press Ctrl + 1 (or Format > Cells)
    3. Click on the "Number" Tab
    4. Select "Custom" option
    5. Click inside the "Type" place holder
    6. Type the desired number format => 0.000
    7. Type a space and then Quote marks ( " )
    8. Press the Apostrophe key ( ' ) twice
    9. Press Quote marks again ( " )
    10. Click OK


    Your Format Code should look like this
    0.000 " '' "


    (after 0.000 you have QUOTEMARK APOSTROPHE APOSTROPHE QUOTEMARK)


    This should give you the best of both worlds without having to do intermediate calculations within your IF.


    HTH.


    m

Participate now!

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

Register Yourself Login