Forum Discussion

Karen Draper's avatar
Karen Draper
Copper Contributor
Mar 05, 2017

Excel Data / Number not recognised

Hi,

 

I exported a csv file from an app and Excel is not recognising the cells data / numbers in several columns no matter what I do.  I have tried all the obvious remedies:

Formatting

Copy / paste

Unlocking worksheet / cells

=TRIM()+0

=CLEAN()+0

Replacing 0, Alt+0160

Text to Colums

 

I have searched for solutions to my problem but have not as yet found one that works in my scenario.

 

All help much appreciated!  Anything to not have to input all the data :-)

excel

4 Replies

  • Deleted's avatar
    Deleted
    Mar 06, 2017

    Hi Karen,

     

    if you have a newer Version of Excel (2013/2016) did you try PowerQuery for importing the CSV file? You have much more possibilities to transform data including date and time formats.

     

    In Excel 2016, select from the menue New Query o fthe Data tab the entry From File and then CSV. After selecting the file, you'll get then a preview windows with some options and you can launch the Query Editor for refining you query.

     

    In Excel 2013, you'll have to activate the PowerQuery COM Add-In before you can use it. The Add-In appears then in its own tab.

     

    Best,

    Mourad

    • Karen Draper's avatar
      Karen Draper
      Copper Contributor
      Mar 06, 2017

      Hi,

       

      Thanks for that.

       

      I have figured it out myself to a certain degree ... have had no experience with PowerQuery as yet thanks for the suggestion, going off to have a look at it now!

       

      K

    • Karen Draper's avatar
      Karen Draper
      Copper Contributor
      Mar 06, 2017

      Hi,

       

      Thanks for responding.

       

      It is .out file not csv as I orginally posted.  I couldn't open the file so copied and pasted the data (from the webpage) instead.

       

      Discovered today that using the paste special "text" option solves it in the interim. Then when I couldn't open the file via Excel I dragged it into an open workbook / worksheet and this too solved my problem!

       

       

      Hours I spent searching for solutions and got them in seconds today :-) Murphy's Law.

       

      Would you have any advice using .out files?

Resources