Many exports of data are not presented to Excel as "perfectly" numerical even when they are intended to be so.
A common thing people do in Excel is to have a value that has been treated as text by Excel and they either enter the editing mode with the cell selected, then leave making and saving no changes, just as you describe, yet the value is immediately changed in whatever final respects by Excel to be solely numerical or they copy a 1 and Paste|Special|Multiply hoping to achieve the same thing.
However, fairly often, neither works. (Folks don't tell you about that on the internet.) There are apparently still some characteristics that Excel is tracking that keep the change from happening. Not talking about non-breaking white spaces here. Just you format a cell as Text, actually personally enter a number, do the above, and it's a number. Do the same to an imported value though and it does not happen.
It seems pretty likely, since your data comes from other programs' exports, that this is what is happening, except that the change DOES occur nicely for you. Which is likely good news.
Try the following: freshly import some data into a fresh spreadsheet (or close, re-open, then import into the offending spreadsheet, but a brand new spreadsheet is better for the experiment since nothing can go wrong). Then type a 1 in some cell, somewhere near and Copy it. Highlight a few of the imported cells and Paste|Special|Multiply.
If the values are suddenly values (for example, the formula suddenly works if you were presenting one of them as the input in A1), then you have data that is "'twixt and 'tween."
If so, you can "clean" the data, so to speak, before use by either importing it elsewhere and doing the Paste|Special|Multiply trick, or import it to its new home, then do it. You can automate that with a macro instead.
If that does not work, it would be very helpful to folks here if you can add to you post a small sample of the just-freshly-imported data, before you've done anything else at all to it.
By the way, if you hear of "non-breaking spaces" or the
CLEAN() function, perhaps from someone in your office, that is defintively NOT the problem here as it would not be solved by entering Edit mode and escaping.