How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Pete Your Own Question
Pete, Computer Expert
Category: Microsoft Office
Satisfied Customers: 17397
Experience:  MS Office expert
Type Your Microsoft Office Question Here...
Pete is online now
A new question is answered every 9 seconds

I am using excel 2016 and have a column of numbers that are

Customer Question

I am using excel 2016 and have a column of numbers that are formatted as general after 4,000 rows I come across a cell that is still formatted as general yet has that little yellow indicator that the cell is formatted as text or preceded by an apostrophe. I tried to select the entire column and convert to a number format these cells will not convert. The only way to change then is to use the convert text to number option the comes up when you click on the yellow box..
Question how did someone do this and is there an easy way to fix it. I have a lot of rows and this is spread in only some places..
Also how can I filter the rows to show where his is happening so I can fix. it..
Submitted: 10 months ago.
Category: Microsoft Office
Expert:  Pete replied 10 months ago.

I am Pete, and I'll be happy to assist with your question today.

Expert:  Pete replied 10 months ago.

Do other people use the spreadsheet apart from yourself?

Could the data have been copied and pasted in, rather than typed?

Expert:  Pete replied 10 months ago.

There is a quick way to convert all cells to number, if you highlight the column downwards starting from the first affected cell, then select the yellow box to convert text to number it will convert all of the selected cells.

I can demonstrate remotely if you would prefer that?

Customer: replied 10 months ago.
Too many rows in too many excel file to use that method
Need to find out how it happened how to detect it and fix globally
On the plain numeric fields can load as text and convert all in either access or ms SQL but the same problem happens with date fields and if I load them as text will not get load errors that are useful.
Expert:  Pete replied 10 months ago.

The cause is how the data has been entered, e.g. if it has been copied and pasted from a text source.

Rows can be highlighted in seconds - this is the only global fix now that the data is already entered and you have formulas on the sheet.

I can demonstrate and fix remotely in seconds if you prefer.

Customer: replied 10 months ago.
Some sheets have over 300,000 rows a few 1,000,000 and in each sheet the location is different and stops and then restarts again a few rows or a few thousand rows later
Also I have several hundred excel files to import. So the highlight method could take days. Would have to make changed load look at error logs and repeat.
Also with all of the manual work the odds of not introducing other data issues is hight
Expert:  Pete replied 10 months ago.

As I have said previously, I can highlight and change ALL affected cells in the sheet within seconds.

Expert:  Pete replied 10 months ago.

Hi Bill, just checking in on your question - did my answer work?

Related Microsoft Office Questions