Office 365: Typing a number into a column formatted for text in Excel still results in a number

[Armenius]

This one has been bugging me ever since my organization switched to Office 365. In literally all prior versions of Excel I've used, typing a number into a column formatted as text would result in that number being formatted in text implicitly. With Office 365 the numbers are being treated as numbers, as if the column is still formatted for General. Clicking on the cell where I just typed the number still shows text in the cell format, but trying to match the numbers between two columns that are formatted differently will result in a FALSE or #N/A. Is there some new setting that I'm not aware of that needs to be set for the expected behavior? I'm trying to train myself to type the apostrophe before the number to force the issue, but I don't think that should be necessary.

You can see the column in my lookup table is text, but my lookup returns #N/A in column V in the second picture. The warning indicator for "number stored as text" is also missing.

Here, I typed the apostrophe and it works, which wasn't necessary when this workbook was first created in the prior version of Excel.

 

[Armenius]

Same thing is happening when I paste as values into a column formatted for text. This one in particular is extremely annoying because now I have to use TEXT to change the value to text before pasting.

 

[techie81]

Text to columns usually fixes this for me.