Microsoft Excel 2003 -irritating problem

  goforit 19:22 23 Oct 2008

I am not sure if this is in the scope of help from PC Advisor but would be grateful for help.

It's small but irritating problem!!!

I have opened a basic excel file supplied for me to teach with.

Two columns show with 'green' corners denoting an error. The error information icon says the numbers in the column are formatted as text. It allows me to select to convert them to number.

But here is the rub. I have to do each one of 60 individually. If I select them all and convert them to number by formatting the cells, the green corner error is still there!

And you should see the solution offered by Microsoft to convert a range of numbers with this problem, it's extraordinary, laughable even. It includes 7 steps, the first of which is to type '1' into an empty cell... it's downhill from thereon in.

If anyone can be kind enough to answer this I would be most grateful.


  Zak 19:31 23 Oct 2008

Go to Tools - Options - Error Checking and i Settings untick "Enable background error checking"

  VoG II 20:00 23 Oct 2008

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste the following into the white space on the right:

Sub ToNos()
With Selection
.NumberFormat = "General"
.Value = .Value
End With
End Sub

Close the VBE using the X, select your range of text numbers, Tools > Macro > Macros, highlight ToNos and click the Run button.

  goforit 20:06 23 Oct 2008

Thanks Zak and VoG. I will use that Tools /Options option to solve it. I thought I should retain that option but it seems a waste of time and serves no purpose.

Thanks VoG for your solution. I will try it out though I am not used to Visual basic except with a tiny bit of macro stuff.

My point is that Microsoft's error flagging and solutions are bonkers!


  goforit 20:06 23 Oct 2008

All is well


  VoG II 20:11 23 Oct 2008

There is an alternative to the macro now that I think of it:

Select the text numbers, Data > Text to Columns, click Next twice then on the third screen tick General and click Finish.

  goforit 20:23 23 Oct 2008

Thanks VoG, you were right. Hope you're around when I have the next Microsoft wrangle. :)


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy S9 review

Wacom Cintiq Pro 24 and 32 review – hands-on

When is the next Apple event?

Les meilleures séries Amazon Prime Video (2018)