Numbers formatted as text - os so you'd think

[Stacy]

I am having a problem with Excel. One column in particular is a "number" formatted like “textâ€. Some of the cells say they are formatted as text, but they are not text. If you try to sort, the program asks you if you would like to sort the "Numbers" and "Text" separately. I have tried formatting the column’s cells as a number, saving, and then reformatting the column & resaving as text. Still, some of the cells say they are text, but are not. The only solution seems to be to turn the column into currency, or scienfitic, and then I am able to see which cells are formatted differently, because their appearance remains unchanged. Then, if I retype the numbers that are in these cells, their fomat matches the appearance of the rest of the numbers in the column - whether it be scientific or currency. Then, I change the entire column back to text. I am linking this spreadsheet as a table in an Access Data Base, but I am unable to trust the results. Any help would be greatly appreciated.

 

[Ken Wright]

Formatting alone will NOT work. If you want to convert a mixed bunch all to
numeric, copy an empty cell, select the entire set of data and do Edit / Paste
Special / Add, and this will then coerce all the data to numeric.

If you would like this entire column or set of data to be text, then select the
data, do Data / Text To Columns / Fixed Width - Hit Next, Hit next again and
then select 'TEXT' from the options top right in the dialog box, then hit
Finish. Your data will now all be text.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I am having a problem with Excel. One column in particular is a "number"

formatted like "text". Some of the cells say they are formatted as text, but
they are not text. If you try to sort, the program asks you if you would like
to sort the "Numbers" and "Text" separately. I have tried formatting the column
's cells as a number, saving, and then reformatting the column & resaving as
text. Still, some of the cells say they are text, but are not. The only
solution seems to be to turn the column into currency, or scienfitic, and then
I am able to see which cells are formatted differently, because their appearance
remains unchanged. Then, if I retype the numbers that are in these cells, their
fomat matches the appearance of the rest of the numbers in the column - whether
it be scientific or currency. Then, I change the entire column back to text. I
am linking this spreadsheet as a table in an Access Data Base, but I am unable
to trust the results. Any help would be greatly appreciated.

 

[Stacy]

Thank you Ken! I will give this a try.

----- Ken Wright wrote: -----

Formatting alone will NOT work. If you want to convert a mixed bunch all to
numeric, copy an empty cell, select the entire set of data and do Edit / Paste
Special / Add, and this will then coerce all the data to numeric.

If you would like this entire column or set of data to be text, then select the
data, do Data / Text To Columns / Fixed Width - Hit Next, Hit next again and
then select 'TEXT' from the options top right in the dialog box, then hit
Finish. Your data will now all be text.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I am having a problem with Excel. One column in particular is a "number"

formatted like "text". Some of the cells say they are formatted as text, but
they are not text. If you try to sort, the program asks you if you would like
to sort the "Numbers" and "Text" separately. I have tried formatting the column
's cells as a number, saving, and then reformatting the column & resaving as
text. Still, some of the cells say they are text, but are not. The only
solution seems to be to turn the column into currency, or scienfitic, and then
I am able to see which cells are formatted differently, because their appearance
remains unchanged. Then, if I retype the numbers that are in these cells, their
fomat matches the appearance of the rest of the numbers in the column - whether
it be scientific or currency. Then, I change the entire column back to text. I
am linking this spreadsheet as a table in an Access Data Base, but I am unable
to trust the results. Any help would be greatly appreciated.

 

[Ken Wright]

Just shout if you have any problems.