Excel changes my text to a number because of an 'E' 1

[IanThomas]

Hi Folks,

I'm at that point in Excel again where I can't seem to find an answer anywhere!

I'm dealing with a set of alphanumeric codes that I'm exporting from a seperate database into Excel as a CSV file.

The problem occurs with some codes that begin with a number & contain an E.

For example, the coded '2E1' becomes '2.00E+01', and the value of the cell changes to 20, so even if I format it after the export, it loses the original code. I cannot format the sheet prior to the export, as it creates it's own sheet for the data at run-time.

So I guess my question is:- is there any way of getting Excel to stop trying to be clever, and just accept what is there, instead of what it wants to see?!

Any help would be greatly appreciated

thanks in advance,

Ian Thomas

 

[shatch]

If you simply highlight all of the cells in the column/row and go to format > cells and choose text they will not be recognised as numbers any more

 

[IanThomas]

The problem is that I cannot format the sheet before the code is entered onto it as it creates the sheet at the same time that it is filling it with data,

so by the time I come to it, it has already decided that "2E1" is going to be a number, and so if I then format the cell back to text, I get the cell value "20" instead of my original "2E1".

As far as I can see, there's no way of telling Excel that I want to format column (x) as text on a new sheet that it has not yet opened.

It's really frustrating!

 

[AnotherHiggins]

IanThomas,

When going through the Text-To-Columns wizard, on Step 3 highlight the trouble column. At the upper right corner, tick the box beside "text". That will force the entire column to read as text and will prevent Excel from converting "2E1" to a number.

shatch,
Your suggestion won't return the already-screwed-up entries back to their original values. E.g. Excel will try to make "2E1" into "2.00E+01". If you change this cell to "text," it becomes "20," not "2E1."

Hope that helps,
John

 

[IanThomas]

Thanks anotherhiggins,

The problem is that I don't get chance to do the 'Text to Columns' wizard - as the database just spits it straight out into Excel, & once it's done that, the data is already incorrect. I was hoping that there might be a secret button in Excel that turned off it's formatting on all new worksheets.

I guess the only solution will be to try & break the link with Excel, & get the database to export it as a *.txt file in notepad/wordpad. Then do the 'text to column' wizard to get it into Excel so that I can point it in the right direction.

Not ideal - but that's computers for you!!!

Thanks again

Ian

 

[AnotherHiggins]

Ian,

Do you have any control over the DB? If so, try exporting as tab-delimited as opposed to comma-delimited. You can still save as .CSV to avoid going through the Data>Get External Data wizard.

This should allow you to go through the Text-To-Columns wizard upon opening the file in Excel. Then, of course, you can automate the whole process with a macro!

John