Numbers not formatted as number in data source

[Vbowman14]

Hey all.
I have a controlled data source generated several times a day from one of our customers. I used the data to verify some of record and update forecasts, etc.

The problem is, of the 100,000 plus cells, ~20,000 cells spread throughout the spreadsheet are not formatted correctly. Double clicking and pressing enter re-enters the value and works fine for anything I then want to do with it. I have researched this quite a bit, and currently using text-to-columns to essentially “re-enter” the values to save time.

I could go into a considerably long back story as to why this method is no longer acceptable, but sparing you the details, is there another way to complete this with a formula or something outside of a macro?

 

[DanteAmor]

Try this:

Write 1 in any one of the blank cells.


Copy this and select the entire range where you have the Number/Text issue.


Now go to Edit > Paste Special


Check the Multiply Radio button
Press OK.


-------------
Another option:

Select all consecutive cells where the warning box appears.
Select Convert to Number