How format number as text

[Woody]

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?

 

[Sean Timmons]

Just highlight the desired cells and format as Text

 

[Woody]

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.

 

[Woody]

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

 

[Al Klein]

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.)

 

[Jacob Skaria]

Hi "Woody"

Try the below and feedback

--From menu Data>From Text>Select 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

 

[Shane Devenshire]

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.