Numbers treated as text

[Jack Sheet]

Sorry folks I feel that this has to be old ground I am revisiting.

I have a third party application that claims to export reports in XLS
format.
It seems to work as intended with one irritating exception.
Some of the numbers in the exported file are treated as text.
If I highlight the cell and check the cell format it claims to be formatted
as "General".
But if I enter in another blank cell the formula
=ISNUMBER(test_cell_ref)
it returns FALSE even though the entry in test_cell_ref appears to be
numeric.
Indeed if I select that cell and hit F2 to edit the cell and then it enter
without making any changes then the above formula thereafter returns TRUE.
I have had this problem before, usually in relation to exported dates, but
until now have not encountered it with simple decimal numbers.

I am looking for a solution that will "audit" the workbook for instances of
cells whose text/number property would change by the action of hitting F2
followed by enter, without any other editing.

This has to be a wheel already invented, assuming that I am not alone in
observing this phenomenon. There may be a compiled add-in available that
does not require a VBA module, but failing that a VBA solution would be
better than nothing.

Any help out there? Thanks.

 

[Gary''s Student]

Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

 

[Jack Sheet]

Thanks - that worked (after including also Dim Count as Long).

Next question:
Is there some way that I can create a button on a toolbar that is
independent of the workbook, so that clicking on the button would run this
macro on the active worksheet? Sorry this is probably beginner stuff.

 

[Jack Sheet]

I think I may have a solution to this that does not require VBA. A bit
kludgy, but here goes:
Select a cell within the worksheet that displays this undesirable property.
It will be fairly easy to find such a cell because it contains a green
warning triangle in the top left corner of the cell.
Hit <Control>+A twice, to select the whole worksheet.
Locate and left click on the warning "!" symbol that should still be
displayed from the time that you had just the one cell selected. From the
menu that pops up as a result, select Convert to number.

 

[Gary''s Student]

Thanks, I dim'ed Count in my version as well.

see:

http://www.mvps.org/dmcritchie/excel/toolbars.htm

for "buttonology"

 

[klmiura]

You are so wonderful, you have no idea how much time this has saved me. I
have put this into my macros and added the button to the toolbar and I have
tested it on several worksheets that I use that have over 35,000 rows and it
works beautifully, I have been looking for this fix for a long time and have
been pasting into word and converting and then back into excel to get the
number formatting to work. As you can imagine I spent countless hourse since
most of my spreadsheets are 35,000+ rows. THANK YOU, THANK YOU!!!!!!!!!!!

Just to be on the safe side I noticed it was stated that something was added
to the macro and since I have never worked in VBA (my assumption of what this
is) I want to be sure I added this in the correct spot, it seems to work
properly; here goes:

Dim r As Range
Dim Count As Long
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

I think I may have a solution to this that does not require VBA. A bit
kludgy, but here goes:
Select a cell within the worksheet that displays this undesirable property.
It will be fairly easy to find such a cell because it contains a green
warning triangle in the top left corner of the cell.
Hit <Control>+A twice, to select the whole worksheet.
Locate and left click on the warning "!" symbol that should still be
displayed from the time that you had just the one cell selected. From the
menu that pops up as a result, select Convert to number.