Convert text in a cell to number

%3CLINGO-SUB%20id%3D%22lingo-sub-1181489%22%20slang%3D%22en-US%22%3EConvert%20text%20in%20a%20cell%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1181489%22%20slang%3D%22en-US%22%3E%3CP%3EA%20colleague%20of%20mine%20opened%20an%20Excel%20spreadsheet%20exported%20from%20SAP.%3CBR%20%2F%3EOne%20column%20contains%20numbers%20only%2C%20but%20is%20recognized%20as%20text.%3C%2FP%3E%3CP%3EShe%20does%20not%20see%20the%20regular%20green%20triangle%20in%20front%2C%20neither%20the%20yellow%20icon%20with%20an%20exclamation%20mark%20that%20gives%20you%20some%20options%20as%20to%20e.g.%20%22convert%20to%20number%22%20or%20%22ignore%20error%22.%26nbsp%3B%20We%20know%20how%20to%20resolve%20that%20through%20a%20formula%2C%20but%20it's%20cumbersome.%3CBR%20%2F%3EShe%20mails%20the%20file%20to%20me%2C%20and%20while%20we%20both%20have%20the%20same%20company%20standard%20setup%20I%20see%20those%20two%20marks%20that%20tell%20me%20that%20I%20need%20to%20convert.%3C%2FP%3E%3CP%3EHow%20come%20that%20the%20two%20variants%20act%20differently%3F%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20we%20can%20force%20it%20to%20show%20those%20marks%20when%20missing%3F%20It's%20so%20more%20easy%20to%20convert!%3C%2FP%3E%3CP%3EBR%3C%2FP%3E%3CP%3EKai%20B%20Isnes%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1181489%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1181517%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20in%20a%20cell%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1181517%22%20slang%3D%22en-US%22%3EYour%20colleague%20has%20Error%20checking%20turned%20off%20in%20File%2C%20Options%2C%20Formulas%20tab%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1181966%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20in%20a%20cell%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1181966%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F259480%22%20target%3D%22_blank%22%3E%40kaibisnes%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20steps%20might%20be%20helpful.%3C%2FP%3E%3CP%3E%3CSTRONG%3EFile%20%26gt%3B%20Options%20%26gt%3B%20Formula%20-%20Error%20Checking%20Rules%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EMake%20sure%20the%20option%20is%20checked%20%22Numbers%20formatted%20as%20text%20or%20preceded%20by%20an%20apostrophe%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_4368a105.png%22%20style%3D%22width%3A%20599px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172021iD2DB9795E7A12EAD%2Fimage-dimensions%2F599x520%3Fv%3D1.0%22%20width%3D%22599%22%20height%3D%22520%22%20title%3D%22Snag_4368a105.png%22%20alt%3D%22Snag_4368a105.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Faraz%20Shaikh%2C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelexciting.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.ExcelExciting.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

A colleague of mine opened an Excel spreadsheet exported from SAP.
One column contains numbers only, but is recognized as text.

She does not see the regular green triangle in front, neither the yellow icon with an exclamation mark that gives you some options as to e.g. "convert to number" or "ignore error".  We know how to resolve that through a formula, but it's cumbersome.
She mails the file to me, and while we both have the same company standard setup I see those two marks that tell me that I need to convert.

How come that the two variants act differently?

Is there any way we can force it to show those marks when missing? It's so more easy to convert!

BR

Kai B Isnes

 

2 Replies
Highlighted
Your colleague has Error checking turned off in File, Options, Formulas tab
Highlighted

Hi @kaibisnes

 

These steps might be helpful.

File > Options > Formula - Error Checking Rules 

Make sure the option is checked "Numbers formatted as text or preceded by an apostrophe"

Snag_4368a105.png

 

Regards, Faraz Shaikh, MCT, MIE, MOS Master, Excel Expert 

www.ExcelExciting.com