Excel Convert text to number (multiple sheets) 1

[rvmams]

Hi,

I have a workbook filled with 40+ sheets with numeric data that is stored as text. I am running excel 2003 and of course it does flag it with the little green corner on each of those cells. Does anyone know of a way to convert all numbers that are stored as text to just numbers without having to do this sheet by sheet?

Thanks,

Roger

 

[AnotherHiggins]

If you want to convert the same range on each sheet, then you can select all sheets (right click on any sheet tab, click on Select All Sheets), and then use the 'times one fix'.

The 'times one fix' is accomplished as follows:
[ul][li]enter 1 into a cell that will is empty on all sheets[/li]
[li]Copy that cell[/li]
[li]highlight the range you want to convert to numbers[/li]
[li]Edit > Paste Special > Multiply[/li][/ul]

NOTE: any empty cells in the range you select will be converted to zeros.

But you probably have different ranges on every sheet. In this case, I think VBA is your only solution.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

yes but dependant on how much data there is, I would advise against it.

For ref, here is how to do it

1: Open workbook with items that need converting
2: Open 2nd, blank workbook
3: Enter a 1 into any blank cell in the new workbook
4: Select ALL sheets to be converted (use SHIFT)
5: Go back to the new workbook and COPY the 1
6: Back to the main workbook and select ALL cells (use the grey box between row 1 and Column A to do this
7: Edit>Paste Special>Values & tick Multiply

If you do indeed have 40 sheets, this will more than liekly completely toast your computer - you are FAR better off doing the same process 1 sheet at a time and not selecting all the cells - just those to be converted

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question