If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Numbers formatted as text - os so you'd think
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.
|
#2
|
|||
|
|||
Numbers formatted as text - os so you'd think
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 :-) ---------------------------------------------------------------------------- "Stacy" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 14/02/2004 |
#3
|
|||
|
|||
Numbers formatted as text - os so you'd think
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 :-) ---------------------------------------------------------------------------- "Stacy" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 14/02/2004 |
#4
|
|||
|
|||
Numbers formatted as text - os so you'd think
Just shout if you have any problems.
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Stacy" wrote in message ... 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 :-) ------------------------------------------------------------------------- --- "Stacy" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 14/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 13/02/2004 |
Thread Tools | |
Display Modes | |
|
|