treat as text

  • Thread starter Thread starter DrunkenSnowMonk
  • Start date Start date
D

DrunkenSnowMonk

hi, i have two questions.
1) there is something you can select in excel so that when you writ
really long numbers it treats it as text and doesn't truncate it or pu
it scientific notation. I was wondering what exactly the menu was an
what needed to be selected. I'm using office 97

2) i have a text file that when i convert to csv and open it up, som
of the information is missing. Espeacially if it seems to begin with
1 or a 0. I'm just wondering how this can be avoided since excel i
what is reading the csv file. Thank you very much.

this problem is really frustrasting me
 
#1. Excel keeps track of 15 significant digits. If your "numbers" exceed 15
digits, you can format the cell as text or prefix your data entry with an
apostrophe.

'12345

(it'll be text.)

If your numbers contain less than 16 digits, you could give that range the
format you want and excel won't "fix" it for you.

#2. When you open a .csv file, excel treats digits as numbers and there's no
difference between 0123 and 123.

If you want to keep the leading 0, you can rename your .csv file to .txt and use
the text to columns wizard and tell excel it's a text field--or you could just
give that column a custom format after you import "000000" (w/o the quotes).

I've never seen xl drop 1's. Any chance your data is being transferred from a
mainframe and that 1 represented a carriage control (new page) symbol? If yes,
and you transferred the data as FBA, most FTP programs will convert that to hex
0C.

Maybe you could transfer an FB dataset instead of an FBA dataset.
 
thank you for your reply dave. funny id didn't get a email abou th
reply. I have anothe question though.
is it possible to merge the contents of two cells
in cell one i have a first name and in cell 2 i have a last name. ho
can i automate it so excel puts the first and last name togather in
cell.


also i noticed that even though excel would give me a scientifi
notaion for my numbers when i selected it it would be the prope
number, any thoughts on that
 
I don't use excelforum. I connect to the ms newsservers directly. So I don't
know why you didn't get an email.

But I think the easiest solution is to use another column with a formula like:

=a1&b1
or
=a1&" "&b1

I'd leave those original columns as-is. It's a lot easier to combine fields
with those formulas than to separate the fields if you need to.

And if you want, you could always hide those columns, so things look pretty.

You didn't ask, but if you had dates or money values you want to concatenate
(string together), you can use:

=text(a1,"$#0.00")&" is the value that's due on: " & text(b1,"mm/dd/yyyy")

(You can even embed your own strings.)
 
Back
Top