Prevent Formatting of a Cell when it is loaded

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to load a worksheet with a Table I created in Word.

One column contains Hexidecimal-like data. It has 300E or B34F and so on.
When it gets a field that contains an E it changes it to Scientifc Notation.
I need it to remain as is.

So when it sees 3E04 I need 3E04 not 3.00+E4

I have been rumaging through the help panels and other resources and cannot
seem to find a way to prevent it from changing this field to what it thinks
it is. I can be considered ASIS data and not a number.
 
Pre-format your cells as "Text", then enter your data.

Select your range of cells
From the Format Menu, choose "Cells..."
On the Number Tab, choose "Text"
Click OK

Now, anything you type in those cells will be treated "as is".

HTH,
Elkar
 
Sorry, I missed the part about importing this table from Word. Are you using
Copy/Paste to do this? If so, use "Paste Special..." instead of Paste.
You'll then have the option to "Paste as Text".

HTH,
Elkar
 
I have tried that and preformatting the Cells to GENERAL. So far, it still
converts the numbers when I paste them.


Some sample Data I have been playing with for pasting:

3E04 SC4999 4999
3E0F SC5000 5000
3E00 SC5001 3E09
3001 SC4001 4001


the 3E04 always becomes 3.00E+04

I have also tried formatting the cell after pasting and it just will not go
back to the 3E04 I want.
 
It works for me with your sample data. Make sure you've done all these steps.

1. Pre-format your destination cells as TEXT (not general).
2. Copy the data in your Word Table
3. Paste Special (not Paste) into your Excel sheet
4. In the Paste Special dialog box, choose the option "Text"

That should work. If it still isn't, what version of Office are you using?
I just tested this on 2003 without problems. Perhaps 2007 or an older
version may behave differently.

HTH,
Elkar
 
Thanks. That did work. Though have to pre-format my cells text and paste
special text seems over kill.

It would be nice if EXCEL did not require all this work just have text data
stay text data.
 
Have you tried saving the Word document as a *.txt file and opening that in
Excel?

The Text to columns Wizard would allow you to designate the data as Text.

Then save as *.xls


Gord Dibben MS Excel MVP
 
Back
Top