Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS!

Microsoft Excel 2003
Problem
Formated Text cells auto-change to Date fields.
Example:
In a list of 11,000 part numbers of past sales the spreadsheet is sorted by
part number then sub-totaled on a change in part number.

The sheet is
1. Copied onto itself as values only.
2. Part Number field Filtered for part numbers that Do Not End With " Total"
3. All filtered rows deleted.
4. Show All leaves all the totaled P/Ns with [part number] Total in the cells.
5. Do a Replace All " Total" with {left blank}

Results
All Part Numbers like Molex Part Number 02-06-2101 get changed to dates.

Once a Cell is Formated as a spicific type of cell, IT SHOULD STAY THAT WAY!!!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Yep, Excel sucks!

MH

"PSSSD" <[email protected]> wrote in message
news:[email protected]...
> Microsoft Excel 2003
> Problem
> Formated Text cells auto-change to Date fields.
> Example:
> In a list of 11,000 part numbers of past sales the spreadsheet is sorted
> by
> part number then sub-totaled on a change in part number.
>
> The sheet is
> 1. Copied onto itself as values only.
> 2. Part Number field Filtered for part numbers that Do Not End With "
> Total"
> 3. All filtered rows deleted.
> 4. Show All leaves all the totaled P/Ns with [part number] Total in the
> cells.
> 5. Do a Replace All " Total" with {left blank}
>
> Results
> All Part Numbers like Molex Part Number 02-06-2101 get changed to dates.
>
> Once a Cell is Formated as a spicific type of cell, IT SHOULD STAY THAT
> WAY!!!
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
> this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

That's interesting....

Try something like this to work around the issue:

Insert a column to one side of your part number column (I'm using
column B, assuming part number data is in column A, beginning with row
2).

Enter this formula in the cell of your new column corresponding to the
first data cell of your part number column (change cell refs to match
your situation):

=IF(EXACT(RIGHT(A2,6)," Total"),LEFT(A2,LEN(A2)-6),"")

Copy it down to your last data row. Only Totals rows will have
anything in the new column.

Select your new column and Copy|Paste Special|Values.

Sort your data based on the new column. You will get a dialog with the
message, "The following sort key may not sort as expected because it
contains some numbers formatted as text. What would you like to do?"
Choose "Sort anything that looks like a number, as a number." This
will cause the Totals rows to move to the top of the list, with the
non-Totals rows underneath.

Delete the non-Totals rows.

Delete your original Part Number Column.

Done!

PSSSD wrote:
> Microsoft Excel 2003
> Problem
> Formated Text cells auto-change to Date fields.
> Example:
> In a list of 11,000 part numbers of past sales the spreadsheet is sorted by
> part number then sub-totaled on a change in part number.
>
> The sheet is
> 1. Copied onto itself as values only.
> 2. Part Number field Filtered for part numbers that Do Not End With " Total"
> 3. All filtered rows deleted.
> 4. Show All leaves all the totaled P/Ns with [part number] Total in the cells.
> 5. Do a Replace All " Total" with {left blank}
>
> Results
> All Part Numbers like Molex Part Number 02-06-2101 get changed to dates.
>
> Once a Cell is Formated as a spicific type of cell, IT SHOULD STAY THAT WAY!!!
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions