A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Numbers formatted as text - os so you'd think



 
 
Thread Tools Display Modes
  #1  
Old February 18th, 2004, 12:51 AM
Stacy
external usenet poster
 
Posts: n/a
Default 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  
Old February 18th, 2004, 01:06 AM
Ken Wright
external usenet poster
 
Posts: n/a
Default 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  
Old February 18th, 2004, 03:46 AM
Stacy
external usenet poster
 
Posts: n/a
Default 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  
Old February 18th, 2004, 01:30 PM
Ken Wright
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.