Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
I am using Excel 2007.
I have several rows in a database and a certain column called XXX. The values in the column are all nubers. However I want them to be thought of as text, not numbers. I have put an apostropy in front of the the value of column XXX in the first row of the workbook, however, I do not want to have to do this manually for each row. There are many rows. I thought I could use the format painter to change the format for all the other rows but if so, I haven't found the combination. Is there an easy way to change all numbers for a column so that Excel thinks of the numbers as text? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
Just highlight the desired cells and format as Text
"Woody" wrote: I am using Excel 2007. I have several rows in a database and a certain column called XXX. The values in the column are all nubers. However I want them to be thought of as text, not numbers. I have put an apostropy in front of the the value of column XXX in the first row of the workbook, however, I do not want to have to do this manually for each row. There are many rows. I thought I could use the format painter to change the format for all the other rows but if so, I haven't found the combination. Is there an easy way to change all numbers for a column so that Excel thinks of the numbers as text? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
OK. Sorry for the density. I figured out if I right click on the cells and
select format, sure enough, the Text option is there. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
Sorry to trouble you again. I only gave you part of the problem because I
was trying to keep it simple. However, the data for the file I am talking about comes in from a .CSV file. The field I am talking about has very long numbers, 14 characters. for one cell, the number up at the top area of Excel reads like this: 12345678901111. But, in the cell itself, it looks like this: 1.23457E+13. This is the value I am trying to get rid of. I want it to read like this: 12345678901111. Formatting to text does not solve the problem. It still looks like 1.23457E+13. I thought, fine, I format it to a number first, then format it to a text. When I format it to a number, the E+13 goes away, but when I then format that to a text, believe it or not, the funny number comes back again (1.23457E+13). Any thoughts on what's happening? It is strange to me that I am having this problem in the first place because the number are surrounded by double quotation marks in the csv file. That is, I can open the csv file in Notepad and see that these numbers are surrounded with quotes. Why Excel insists on bringing them in as numbers I don't know. Any thoughts |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
Woody wrote:
Sorry to trouble you again. I only gave you part of the problem because I was trying to keep it simple. However, the data for the file I am talking about comes in from a .CSV file. The field I am talking about has very long numbers, 14 characters. Excel can only hold an 11 (decimal) digit mantissa. If the number has more digits, Excel stores it as an 11 digit mantissa and an exponent, so the digits aren't there for you to convert to text. (Formatting only changes how the display looks, conversion [like =TEXT()] changes the actual data.) It is strange to me that I am having this problem in the first place because the number are surrounded by double quotation marks in the csv file. That is, I can open the csv file in Notepad and see that these numbers are surrounded with quotes. Why Excel insists on bringing them in as numbers I don't know. Excel is too smart. It evaluates what's between the quotes and, if it looks like a number, the column becomes numeric (in storage, regaredless of how you format it). You could try to add about 10 rows at the top of the .csv file (just copy the first row, paste it, change the first item to "dummy", and change "row XXX" to real text). That might force Excel to set row XXX as a text column. (It works in Access, but Excel may just make it a General column, which gives you the same problem.) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
Hi "Woody"
Try the below and feedback --From menu DataFrom TextSelect the .csv file to be opened. --From the 'Text Import Wizard' Step 1; select 'Delimited' and hit next --From the 'Text Import Wizard' Step 2; select 'Comma' and hit next (you can uncheck the other defaults selected) --From the 'Text Import Wizard' Step 3; From 'Data Preview Area' select all the columns which you would want to be displayed as text. Once selected the background will be black and the text will be white. --Then on the same window from the group 'Column Data format' select Text and Hit Finish. If this post helps click Yes --------------- Jacob Skaria "Woody" wrote: I am using Excel 2007. I have several rows in a database and a certain column called XXX. The values in the column are all nubers. However I want them to be thought of as text, not numbers. I have put an apostropy in front of the the value of column XXX in the first row of the workbook, however, I do not want to have to do this manually for each row. There are many rows. I thought I could use the format painter to change the format for all the other rows but if so, I haven't found the combination. Is there an easy way to change all numbers for a column so that Excel thinks of the numbers as text? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How format number as text
Hi,
First, you can't change a number to text by changing the format. Second, Excel does not store 11 decimals but up to 15, here are some limits from Excel's help: Number precision 15 digits Smallest allowed negative number -2.2251E-308 Smallest allowed positive number 2.2251E-308 Largest allowed positive number 9.99999999999999E+307 Largest allowed negative number -9.99999999999999E+307 Largest allowed positive number via formula 1.7976931348623158e+308 Largest allowed negative number via formula -1.7976931348623158e+308 The reason you are seeing 1.23457E+13 is because the format of the cell and/or column width is such that the number is being displayed in scientific notation. To see your number as 12345678901111 simply widen the column and choose Format, Cells, Number and set the decimals to 0. The only time you need to format a cell as Text is if the number is longer than 15 digits or there are leading 0's you want to retain. If that is the case the easiest way to convert the numbers to text is to use a formula such as =A1&"", copy it down and then copy all the formulas and paste them as values. Then get rid of the original numbers. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Woody" wrote: Sorry to trouble you again. I only gave you part of the problem because I was trying to keep it simple. However, the data for the file I am talking about comes in from a .CSV file. The field I am talking about has very long numbers, 14 characters. for one cell, the number up at the top area of Excel reads like this: 12345678901111. But, in the cell itself, it looks like this: 1.23457E+13. This is the value I am trying to get rid of. I want it to read like this: 12345678901111. Formatting to text does not solve the problem. It still looks like 1.23457E+13. I thought, fine, I format it to a number first, then format it to a text. When I format it to a number, the E+13 goes away, but when I then format that to a text, believe it or not, the funny number comes back again (1.23457E+13). Any thoughts on what's happening? It is strange to me that I am having this problem in the first place because the number are surrounded by double quotation marks in the csv file. That is, I can open the csv file in Notepad and see that these numbers are surrounded with quotes. Why Excel insists on bringing them in as numbers I don't know. Any thoughts |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
Hoe to change text format .126 to number format 0.126 | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
How do i change numbers in text format to number format? | New Users to Excel |