Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

excel numbers

Options
  • 19-07-2019 3:49pm
    #1
    tritriagain
    Registered Users Posts: 1,156 ✭✭✭


    ì have a column of numbers and most of them are ok but there is a certain amount of them that when i select the appear in the function bar with quotations i.e 1234 would appear as ="1234" and is affecting the way my linked sheets handle the data. probably simple but cant figure it out . tried formattinjg cells but this doesnt seem to work.


Comments

  • Options
    #2
    Baz_
    Registered Users Posts: 2,660 ✭✭✭Baz_


    tritriagain wrote: »
    too long to quote.

    First question that comes to mind, how did the values get in there? Manually typed, copied and pasted, some kind of automated process.

    Second question if you manually remove the double quotes is the cell then properly populated with a number?


  • Options
    #3
    namenotavailablE
    Registered Users Posts: 59,556 ✭✭✭✭namenotavailablE


    You could try Paste Special by multiplying each existing number by 1. You do this as follows:

    1. Type the number 1 in a blank cell.
    2. Copy it.
    3. Select your column of values.
    4. Click the arrow beside the 'Paste icon' and choose 'Paste special'
    5. Choose the 'Multiply' radio button and press OK

    What this does is forcibly convert any numbers displaying as text into actual numbers.

    Free Excel net pay calculator- https://taxcalc.eu/monthlyss/Employee%20PAYE%20calculator.xlsm



  • Options
    #4
    The Tetrarch
    Registered Users Posts: 2,092 ✭✭✭The Tetrarch


    This looks like data pasted from a source (probably a web page).
    Excel interpreted it as text probably because some of the data is text, or the first lines are text.

    An example would be part numbers
    BAC15921-2
    AN7500B
    65000-3

    You might get a few treated as text, others treated as numbers, and in this case 65000-3 is treated as a subtraction.
    BAC15921-2
    AN7500B
    64997

    Use the ..... /Data/Text To Columns/ menu option, and select Text for the column if you want it to read as text.
    Compare the Excel result with the source.
    I think if you format the Excel column as general the text will align left and numbers will alight right (and you know you have a mixture of text and numbers i.e. the data is wrong).


Advertisement