text format green error triangle set-up issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2046975%22%20slang%3D%22en-US%22%3Etext%20format%20green%20error%20triangle%20set-up%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046975%22%20slang%3D%22en-US%22%3E%3CP%3Ewhen%20importing%20a%20text%20file%20into%20excel%2C%20i%20do%20not%20get%20the%26nbsp%3Btext%20format%20green%20error%20triangle%2C%20which%20i%20could%20then%20click%20on%20and%20select%20%22Convert%20to%20number%22%2C%20even%20though%20I%20have%20this%20specific%20error%20checking%20rule%20selected%20in%20the%20excel%20options.%20How%20do%20i%20get%20it%20to%20appear%2Fwork%20please%3F%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20of%20my%20inability%20to%20do%20this%20conversion%2C%20i%20am%20unable%20to%20format%20the%20cells%20any%20further.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20open%20the%20same%20file%20on%20another%20computer%20with%20the%20same%20Family%20office%20365%20license%2C%20it%20does%20show%20the%20green%20triangles%20and%20i%20am%20able%20to%20do%20the%20conversion%20to%20numbers.%20It%20therefore%20leads%20me%20to%20believe%20that%20there%20must%20be%20some%20other%20setting%20overriding%20the%20excel%20option%20%22Error%20checking%20rules%2C%20clear%20the%20Numbers%20formatted%20as%20text%20or%20preceded%20by%20an%20apostrophe%20check%20box%22.%20Anyone%20aware%20what%20setting%20could%20this%20be%3F%20or%20anyone%20aware%20of%20any%20other%20solution%3F%20Tried%20below%20recommended%20solutions%20with%20not%20success%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ffix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0%23__toc260840020%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ffix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0%23__toc260840020%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046975%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2047033%22%20slang%3D%22en-US%22%3ERe%3A%20text%20format%20green%20error%20triangle%20set-up%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2047033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923959%22%20target%3D%22_blank%22%3E%40Petulka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFile-%26gt%3BOptions%20and%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20822px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245143i836AF048F272AADD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

when importing a text file into excel, i do not get the text format green error triangle, which i could then click on and select "Convert to number", even though I have this specific error checking rule selected in the excel options. How do i get it to appear/work please? 

Because of my inability to do this conversion, i am unable to format the cells any further. 

If i open the same file on another computer with the same Family office 365 license, it does show the green triangles and i am able to do the conversion to numbers. It therefore leads me to believe that there must be some other setting overriding the excel option "Error checking rules, clear the Numbers formatted as text or preceded by an apostrophe check box". Anyone aware what setting could this be? or anyone aware of any other solution? Tried below recommended solutions with not success: https://support.microsoft.com/en-us/office/fix-text-formatted-numbers-by-applying-a-number-format-65...

4 Replies

@Petulka 

File->Options and here

image.png

@Sergei Baklan yes, this was selected. after contacting Microsoft support and several hours of tweaking, it turns out when you use shared files on 2 PC with different windows language, this feature doesn't work as intended due to the windows ignoring the regional settings of the file as such including this function, even if selected in options like you suggested.

@Petulka 

The key point is how do you share the file - is that network share; or homegroup share when file is located on one of PC; or OneDrive share with it's own language/regional settings.

@Sergei Baklanunfortunately this is incorrect, we tried OneDrive, Dropbox and local copies, no difference, exact the same issue, until we changed the 2nd language windows to match the main one. This is a result of 2x4+ hours with Microsoft support including 2nd level support FYI