Cell proceeeded by an apostrophe

[Donal28]

Hi All

I'm trying to run formulas for reports on spreasheets but am finding that the format of all the numbers on these spreadsheets are Cell proceeeded by an apostrophe. I've tried a find an replace the for these with blank spaces but it isn't recognising the apostrophes fopr some reason. The fomula I'm trying to run is

=COUNTIF(O:O,0)

Any help on this would be very much appreciated

Regards
Donal

 

[Richard Schollar]

Hi Donal

Select the column and go Data>TextToColumns>Finish. This should convert all the 'numbers' to proper numbers.

Your countif will only count O col cells which have the value 0.

 

[energman58]

Try

=COUNTIF(A:A,"=0")

the inverted commas in the countif formula are required

But beware!

Any solution like the one below

=SUMPRODUCT(--(VALUE(A:A)=0))

Or a solution which forces a change to numeric (like *1)

will all count blanks as zero as it will turn all of them into numbers so you will get an answer of about a million as it picks up all the empty cells below your data.

 

[Donal28]

Hi Donal

Select the column and go Data>TextToColumns>Finish. This should convert all the 'numbers' to proper numbers.

Your countif will only count O col cells which have the value 0.

Thanks for the reply Richard, on the sheet there is numerous columns of numbers. Is it possible to do all these at one time?

 

[Donal28]

Try

=COUNTIF(A:A,"=0")

the inverted commas in the countif formula are required

But beware!

Any solution like the one below

=SUMPRODUCT(--(VALUE(A:A)=0))

Or a solution which forces a change to numeric (like *1)

will all count blanks as zero as it will turn all of them into numbers so you will get an answer of about a million as it picks up all the empty cells below your data.

Thanks for the reply....i'm just getting a circular reference error when I use

=COUNTIF(A:A,"=0")

 

[Richard Schollar]

Hi Donal

Select all the cells in one big contiguous range (ie no multiple selections) and then open up the Visual Basic Editor (Alt+F11) and open up the Immediate Window (Ctrl+G) and type in the following:

Selection.Formula = Selection.Value

this *should* convert all the text 'numbers' to numeric values.

 

[Donal28]

Hi Donal

Select all the cells in one big contiguous range (ie no multiple selections) and then open up the Visual Basic Editor (Alt+F11) and open up the Immediate Window (Ctrl+G) and type in the following:

Selection.Formula = Selection.Value

this *should* convert all the text 'numbers' to numeric values.

Yes this has worked, thanks . There are numerous sheets that I will have to do this on....Is there a quick way I do this on multiple sheets instead of doing just one at a time?

 

[Richard Schollar]

Depends on whether you have any merged cells on your sheets - if not then:

For Each ws In Worksheets:ws.UsedRange.Formula = ws.UsedRange.Value:Next ws

entered in the Immediate Window will work.

 

[Donal28]

Depends on whether you have any merged cells on your sheets - if not then:

For Each ws In Worksheets:ws.UsedRange.Formula = ws.UsedRange.Value:Next ws

entered in the Immediate Window will work.

Theses spreadsheets reside in different folders example of locations and names of the spreadsheets below:

C:\\irportcdcom\public\TMDS Downloads\TS1\22001 TMDS 11.05.16 B12.xls
C:\\irportcdcom\public\TMDS Downloads\TS2\22002 TMDS 11.05.16 B12.xls
C:\\irportcdcom\public\TMDS Downloads\TS3\22003 TMDS 11.05.16 B12.xls

Is it possible to have a macro access these sheets and run For Each ws In Worksheets:ws.UsedRange.Formula = ws.UsedRange.Value:Next ws?