最佳解答者
very interest issue of Excel

問題
解答
-
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:- Open the Excel > new blank sheet
- Data > Import External Data > Import Data > open the file
- Choose "Delimited" and press next
- Use "comma" as the delimiter and press next (Suppose your file should be comma separated CSV format )
- Find the columns which are containing number in format, label them into "Text"
- Finish
Thanks
所有回覆
-
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. -
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. -
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
-
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:- Open the Excel > new blank sheet
- Data > Import External Data > Import Data > open the file
- Choose "Delimited" and press next
- Use "comma" as the delimiter and press next (Suppose your file should be comma separated CSV format )
- Find the columns which are containing number in format, label them into "Text"
- Finish
Thanks -
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