Home

Opening a CSV in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-717421%22%20slang%3D%22en-US%22%3EOpening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717421%22%20slang%3D%22en-US%22%3E%3CP%3EI%20opened%20a%20CSV-file%20in%20Excel%20365.%20The%20problem%20is%20that%20negative%20numbers%20do%20not%20convert%20in%20the%20cells.%20The%20result%20is%20%23VELD!%20instead%20of%20the%20negative%20number.%20Whats%20the%20problem%20and%20how%20to%20solve%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-717421%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717717%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365666%22%20target%3D%22_blank%22%3E%40KeesW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENormally%20Excel%20recognizes%20negative%20numbers%20in%20CSV%20correctly.%20Could%20you%20attach%20some%20sample%20with%20few%20lines%20in%20question%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-717950%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHere%20an%20example%20of%20the%20CSV%20file%20as%20attachment%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718004%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365666%22%20target%3D%22_blank%22%3E%40KeesW%3C%2FA%3E%26nbsp%3BOr%20try%20this%20file%20to%20import%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718223%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365666%22%20target%3D%22_blank%22%3E%40KeesW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20able%20to%20open%20it%20without%20any%20problems%2C%20as%20well%20as%20import%20both%20by%20legacy%20and%20Power%20Query%20connectors.%20Result%20saved%20as%20xlsx%20is%20attached.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20which%20version%20of%20Excel%20you%20are%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718476%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718476%22%20slang%3D%22en-US%22%3E%3CP%3EI%20will%20send%20you%20an%20screenshot%20of%20my%20excel.%20See%20the%20attachments.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718477%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718477%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20Microsoft%20Excel%20(MS%20365)%20for%20Mac%20version%2016.26%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718554%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365666%22%20target%3D%22_blank%22%3E%40KeesW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20that's%20an%20issue%20like%20here.%20I%20tried%20to%20play%20with%20different%20options%2C%20but%20I'm%20on%20Windows%20and%20on%20English%20version%20-%20was%20not%20able%20to%20reproduce%20an%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-718615%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20a%20bug.%3C%2FP%3E%3CP%3EIt's%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-field-error-66b3a5db-1904-41aa-9913-51ded9dd15f2%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%23FIELD!%20error%20type.%3C%2FA%3E%3C%2FP%3E%3CP%3EIt%20is%20triggered%20on%20systems%20where%20the%20.%20(dot)%20is%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20the%20decimal%20separator%20and%20Excel%20interprets%20it%20as%20a%20formula%20because%20of%20the%20-%20(minus%20sign).%20And%20because%20the%20formula%20contains%20a%20.%20(dot)%20Excel%20thinks%20this%20a%20reference%20to%20a%20linked%20data%20type.%20Of%20course%20there%20is%20no%20such%20linked%20data%20type%20or%20data%20type%20field%20and%20Excel%20throws%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20happens%20when%20you%20use%20text%20to%20columns%20with%20unchanged%20options.%3C%2FP%3E%3CP%3EIf%20you%20change%20the%20format%20of%20the%20columns%20to%20text%20in%20the%20third%20step%20there%20will%20be%20no%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20no%20problems%20with%20Power%20Query%20but%20that%20is%20not%20yet%20available%20to%20Macs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719316%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719316%22%20slang%3D%22en-US%22%3E%3CP%3EIndeed%2C%20it%20looks%20like%20a%20bug.%20Thank%20you%20for%20the%20solution%2C%20it%20works%20by%20changing%20the%20format%20to%20Text%20in%20the%203rd%20step.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719931%22%20slang%3D%22en-US%22%3ERe%3A%20Opening%20a%20CSV%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20you%20are%20right.%20I%20tried%20to%20play%20with%20decimal%20separator%2C%20but%20my%20Excel%20automatically%20converts%20such%20column%20to%20text%20and%20gives%20no%20error.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20307px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120583i8D237C2DE243E466%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
KeesW
Occasional Contributor

I opened a CSV-file in Excel 365. The problem is that negative numbers do not convert in the cells. The result is #VELD! instead of the negative number. Whats the problem and how to solve it?

10 Replies

@KeesW 

Normally Excel recognizes negative numbers in CSV correctly. Could you attach some sample with few lines in question?

@Sergei Baklan Here an example of the CSV file as attachment

@KeesW Or try this file to import in Excel.

@KeesW 

 

I'm able to open it without any problems, as well as import both by legacy and Power Query connectors. Result saved as xlsx is attached. 

 

On which version of Excel you are?

I will send you an screenshot of my excel. See the attachments.@Sergei Baklan 

I use Microsoft Excel (MS 365) for Mac version 16.26@Sergei Baklan 

Highlighted

@KeesW 

Perhaps that's an issue like here. I tried to play with different options, but I'm on Windows and on English version - was not able to reproduce an error.

@Sergei Baklan 

It looks like a bug.

It's the #FIELD! error type.

It is triggered on systems where the . (dot) is not the decimal separator and Excel interprets it as a formula because of the - (minus sign). And because the formula contains a . (dot) Excel thinks this a reference to a linked data type. Of course there is no such linked data type or data type field and Excel throws an error.

 

This happens when you use text to columns with unchanged options.

If you change the format of the columns to text in the third step there will be no error.

 

And no problems with Power Query but that is not yet available to Macs.

 

Indeed, it looks like a bug. Thank you for the solution, it works by changing the format to Text in the 3rd step.@Detlef Lewin 

@Detlef Lewin 

 

It looks like you are right. I tried to play with decimal separator, but my Excel automatically converts such column to text and gives no error.

image.png

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies