Excel question

shopbot

I am working on a large Excel spreadsheet. About half of the cells have green shading in the top left corner which I believe means that the numbers in the cell are stored as text (as opposed to numbers). I need to use the numbers in these cells in formulas but cannot presently do this because they are stored as text. I have worked out to convert each cell at a time but how do I convert all from text to numbers?

Thanks

SB

Mr_Skint_2

Click grey box top left between A and 1 or select a column or row
then choose format cells and choose desired effect...Number,currency Accounting
Date so on and so forth....

I think its just been so long..........

shopbot

Thanks Mr S - as quick as ever!

That does'nt work although it seems the obvious way. The way to correct an individual cell is to click on the box and then a little box appears at the side with some options including the conversion one. There are two many cells on this sheet to do individually. As a complication some columns have to stay tect and others need to be numbers.

Oh my brain hurts

Mr_Skint_2

What about hold down Ctrl and a mouse click on each cell
then convert the lot in one go... Blimey im a tad rusty...

shopbot

Still no luck...................:mad:

Mr_Skint_2

Abacus maybe.......

shopbot

Okay a solution of sorts:
Find a blank cell. Paste the number 1 into it. Copy this cell. Highlight the cells you need to convert from text to number and then right click for Paste Special and then select multiply. Bingo!

This can't be the quickest way though......

and now the converted cells show as ## :mad:

Mr_Skint_2

shopbot wrote:

Okay a solution of sorts:
Find a blank cell. Paste the number 1 into it. Copy this cell. Highlight the cells you need to convert from text to number and then right click for Paste Special and then select multiply. Bingo!

This can't be the quickest way though......

and now the converted cells show as ## :mad:

Just found this http://exceltips.vitalnews.com/Pages/T0761_Converting_Text_to_Values.html

shopbot

Thanks for the link.............

I think it's best to leave this spreadsheet until the morning now before I have the computer equivalent of when Basil Fawlty beats his car with a branch.

Cerbera_2

Great link Mr Skint :T
You don't have anymore for Word, Powerpoint, etc by any chance?

BTW brilliant avatar :eek:

scheming_gypsy

shopbot wrote:

and now the converted cells show as ## :mad:

i'm no expert on Excel but doesn't #### usually mean that the cell isn't wide enough to fit the number?