very interest issue of Excel

所有回覆

• 

  

  0

  登入以投票

  Hi Tom,
  
  I think you can try to select the Cell/column or rows then you want to enter Text value,
  Then Click on "Format" on the Tool bar
  Click on - >"Cells".
  On the "Format Cells" window's "Category:" Combo box,
  Select "Text" then click "OK"
  
  Then it should allow you then enter "18191E1" as text, otherwise it will convert it to something else.
  
  Hope this help.
  

  2008年7月2日 上午 07:57

  回覆

  |

  引述
• 

  

  0

  登入以投票

   

  Thanks Chi,

   

  But as those data I extracted from other database, but not directly input to excel manually.

  So I only can find a way to convert back to expected value in cell.......

  Any other idea ?

  2008年7月2日 上午 08:29

  回覆

  |

  引述
• 

  

  0

  登入以投票

  Hi Tom
  
  Could you let me know how do you put the data from other database on excel, through csv? Access?
  is that Spreadsheet generate by another program/application?
  
  I am not sure if the anyway it could convert it back to expected value. as When I enter the value "18191E1", it automatically convert it to 181910.
  
  
  

  2008年7月2日 上午 08:46

  回覆

  |

  引述
• 

  

  0

  登入以投票

   

  Hi ChiYau,

   

  Appreciate your help.

  I extracted those data from our ERP system front end interface, not extracted from the database atucally. And so there is no way to format the cell before I export out.

  I only can think about anyway can correct this after data existed in sheet.

   

  Chris

  2008年7月2日 下午 04:13

  回覆

  |

  引述
• 

  

  0

  登入以投票

  I am not sure the progress you extract the spreadsheet file from the ERP system. But I think it should be a old version of excel or the CSV format after it extracted.
  
  Generally, if you just simply double click the file, all of the numeric format of data will auto converted. However, you can try this way to import your spreadsheet into excel without being auto converted.
  
  

  Please follow the steps below:

  1. Open the Excel > new blank sheet
  2. Data > Import External Data > Import Data > open the file 
  3. Choose "Delimited" and press next
  4. Use "comma" as the delimiter and press next (Suppose your file should be comma separated CSV format )
  5. Find the columns which are containing number in format, label them into "Text"
  6. Finish

  Thanks

  
  

  2008年7月9日 上午 06:08

  回覆

  |

  引述
• 

  

  0

  登入以投票

   

  Thanks CyrusSSss,,,,,it seems the only way to resolve it.

  2008年7月9日 上午 11:29

  回覆

  |

  引述
• 

  

  0

  登入以投票

  Hi,

   

  I have another thought.  If i) the number before "E" always has 5 digits and ii) the letter is always "E", we can convert the column of numbers back to the original strings with the following formula:

   

  =LEFT(A1,5)&"E"&(LEN(A1)-5)

   

  I've tried 18191E1, 20190E2 and 12345E10.  All are okay.

   

  Regards,

  Carson

  2008年7月12日 上午 08:42

  回覆

  |

  引述