No Date Conversion Please!

  • Thread starter Thread starter Ellen
  • Start date Start date
E

Ellen

Hello,
I have a web page that I want to convert to an Excel pread
sheet. In it there is a table that contains ID#s. An
example of one of the ID#s is 03-2254, which Excel
converts to "Mar-54". Is there a way for me to keep the
ID#s as is and not have them converted into some sort of a
date?

In an earlier reply to my post, I was told to use the text
import wizard and in the third screen format the column
to "text". I found the import wizard in the Data pull
down and couldn't find the referenced third screen. When
I ran this wizard the funny conversion still happened.
Does anyone have any other suggestions?

Thank you in advance,
Ellen
..
 
Ellen wrote...
I have a web page that I want to convert to an Excel pread
sheet. In it there is a table that contains ID#s. An example of
one of the ID#s is 03-2254, which Excel converts to "Mar-54". Is
there a way for me to keep the ID#s as is and not have them
converted into some sort of a date?

In an earlier reply to my post, I was told to use the text import
wizard and in the third screen format the column to "text". I
found the import wizard in the Data pull down and couldn't find
the referenced third screen. When I ran this wizard the funny
conversion still happened.
Click to expand...
...

I believe you were also told that the text import wizard needed to wor
with plain text files rather than HTML files. That so, you'd either nee
to convert the HTML files to plain text or import the HTML data with th
conversions, then reverse the conversions after the fact.

For the latter, if your imported range, which I'll assume i
Sheet1!A1:J100, has nothing in it that should be considered dates
insert a new worksheet, select A1:J100 in that worksheet, with cell A
active within the selection type the formula

=IF(LEFT(CELL("Format",Sheet1!A1),1)="D",TEXT(Sheet1!A1,"m-yyyy"),Sheet1!A1)

and hold down the [Ctrl] key before pressing [Enter]. This shoul
return your original field text values for all cells in Sheet1!A1:J10
that contain dates and the actual values of all other cells in you
imported data range. Run the Edit > Copy menu command, switch back t
Sheet1, select A1:J100, and run the Edit > Paste Special menu command
select Values from the Paste Special dialog, and click OK. Finally
delete the added worksheet
 
hgrove wrote...
...
=IF(LEFT(CELL("Format",Sheet1!A1),1)="D",TEXT(Sheet1!A1,"m-yyyy"),Sheet1!A1)
Click to expand...

Oops, didn't notice the leading zero! Make that

=IF(LEFT(CELL("Format",Sheet1!A1),1)="D",TEXT(Sheet1!A1,"mm-yyyy"),Sheet1!A1
 
Create a custom number format within Format-Cells that reads 00-0000. Apply
this format to the column with the ID numbers--after you have put the
numbers into the Excel sheet.
 
Richard O. Neville wrote...
Create a custom number format within Format-Cells that reads
00-0000. Apply this format to the column with the ID numbers--
after you have put the numbers into the Excel sheet.
Click to expand...

The OP's problem is unwanted *date* conversion, so if the imported tex
03-2254 were converted into the date [1-]Mar-2254, Excel would store th
date serial value 129357. Formatting that as you suggest would displa
12-9357. Not what the OP requested
 
Back
Top