Microsoft Excel 2003 -irritating problem

  goforit 19:22 23 Oct 08

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 08

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

  VoG II 20:00 23 Oct 08

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 08

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 08

All is well


  VoG II 20:11 23 Oct 08

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 08

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

VFX Oscar nominees 2017: Discover how the visual effects were created

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…