Excel help…

Home Forum Chat Forum Excel help…

Viewing 16 posts - 1 through 16 (of 16 total)
  • Excel help…
  • soma_rich
    Member

    Is it formatted as text? Right click the column and click format cells choose a number.

    Premier Icon Hobster
    Subscriber

    If you click on the lefthand column does all the data appear in there?

    If so you need to convert so its using multiple cells per line of data.

    Looks like you’ve got a space after the number. Try removing this

    phildaws
    Member

    Like Hobster says, if all the text is in a single cell, you can use the “Text to Column” function to split it to multiple ones.

    clubber
    Member

    It looks like you’ve got all the text in each line as text – so the cell actually contains “26/02/2013 VIS LV INSUREFRIZZEL 336.77 “

    You need to split it up into columns by clicking Data then “text to columns”

    lucien
    Member

    Thanks for the replies –
    It’s not a good pasta/pic in my OP, it is in separate columns, and there is no space on the left hand side of each column either…..

    Still stuck….

    clubber
    Member

    Try copying the text again, paste it into notepad then copy that and paste it into a new spreadsheet (and then probably do the data->Text to columns bit)

    Sometimes, there’s funny formatting. Pasting it into notepad will remove all formatting.

    irelanst
    Member

    As mentioned above, I would suspect it thinks the numbers are text. Even if you format the column it doesn’t always make the conversion until you edit the text in each cell(so for example if you type in the number).

    If the list is short then go down each cell in turn pressing F2 then enter, if it’s a long list then in another column just multiply the value by 1, this should convert to a number.

    lucien
    Member

    Ok, seems simple, but this is really doing my head in

    Cut and pasted (from web browser) a table of figures and put in Excel.

    Re-formatted the worksheet, cleared all previous formatting, and put into a sensible shape.

    Trying to add up a column, and it won’t recognise the numbers in the column ie that they are in there; Excel seems to think the cell is empty. If I manually over-type the same number it recognises this, and add’s it to the total.

    I’ve tried to copy out the numbers, put into word, clear formatting and paste back in – same problem.

    Any ideas how I can get Excel to recognise the cells as having a number in them?

    Here’s a selection of data from spreadsheet.

    26/02/2013 VIS LV INSUREFRIZZEL 336.77 
    26/02/2013 VIS INT’L 0078260494 35.40 
    26/02/2013 VIS INT’L 0078260493 37.58 
    26/02/2013 VIS INT’L 0078260495 40.75 

    jfletch
    Member

    =VALUE(…)

    will give the numerical value of a text cell,.

    One neat way to see if numbers are being stored as text is to check there is no formatting on the cell. Then text will be left alligned and numbers will be right alligned.

    baby
    Member

    and there is no space on the left hand side of each column either…..

    But what about on the right hand side?

    Looks like you’ve got a space after the number

    ^ As above, notepad is your friend. Excel is a %#£&$”! fascist.

    Premier Icon Stoner
    Subscriber

    apply and reverse a mathematical function to the text number. Excel is then able to treat it as a number.

    ie. =A1*1000/1000

    Use TRIM if you need to clear unnecessary spaces.

    Use TRIM if you need to clear unnecessary spaces.

    Or find & replace e.g. find ” ” & replace “”.

    joemc
    Member

    …Or type a ‘1’ into a spare cell, copy it and ‘paste special > multiply’ over all the numbers-stored-as-text cells. In many cases this forces them to numeric format.

    As jletch said – try = VALUE(<cell>) in an adjoining column

    I’m guessing that when you double click on the cell you’ll see the value prefixed with a ‘ which will mean it will ignore any attempts to format it as a number.

Viewing 16 posts - 1 through 16 (of 16 total)

The topic ‘Excel help…’ is closed to new replies.