Microsoft Excel 2003 -irritating problem

  goforit 19:22 23 Oct 08
Locked

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.

Sophie

  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!

Sophie

  goforit 20:06 23 Oct 08

All is well

Sophie

  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. :)

Sophie

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5