Converting a number to a string

[Joakim]

Hi everyone!

I have this problem. I need to create an excel document
that contains two columns, one with merely letters (A-z)
and one with a combination of 4 digit numbers (0000-9999)
however when i write "0210" its accepted as "210", that
is, the leading zero is removed....i want this column, or
this number to be more exact, to be stored as text so
that when i write 0210 it will really say 0210 in the
field and not 210. How do I do this ?

Thanks
/Joakim

 

[Satish Nair]

select the column in which which u want to enter numeric
data as text, then select format-cell-text. thereafter
all the numbers that you enter will be formated as text
and you will not loose the leading zeros.

 

[Anon]

Hi everyone!

I have this problem. I need to create an excel document
that contains two columns, one with merely letters (A-z)
and one with a combination of 4 digit numbers (0000-9999)
however when i write "0210" its accepted as "210", that
is, the leading zero is removed....i want this column, or
this number to be more exact, to be stored as text so
that when i write 0210 it will really say 0210 in the
field and not 210. How do I do this ?

Thanks
/Joakim

You can format the column as text BEFORE entering the data. Data will then
be text, not numbers.
Or you can precede each entry with an apostrophe. Data entered as '0210
will show in the cell as 0210.
Or you can use a custom number format 0000 for the column of numbers,
applied before or after entering them. Data will remain as numbers.

 

[Joakim]

Thanks a lot for your help, but is there anyway you can
do it "textwise".. My problem is that i have a semicolon
separated file, that kinda looks like this:
hello/tto/t0210/n
that i name *.xls. When opening this file in excel it
separates the text giving me three columns
hello to 210

so i cannot make any setting in excel before i fill the
columns..i basically write the data to a textfile that i
name *.xls. Is there anyway i can write the string so
that it wont see it as a number but rather a string ?

Thank you
/joakim

 

[Joakim]

Hi!

Thank you for the help. I have tried writing the "number"
preceeded by an apostrophe, and that works fine when i do
it "in excel". My problem is that i first write a
textfile from an application i am developing. This
textfile is semicolonseparated and separated with tab and
new line characters. I.eg it could look like this:
Hello/tto/t0210

After i have filled the file i name it *.xls and open it
in excel. This gives me three columns:
Hello to 210

If i write it preceeded by an apostrophe the apostrophe
shows...its says '0210 instead of 0210 which it says if i
write it in the excelapplication...is there anyway i can
do it ?

Thanks
/Joakim

 

[Anon]

Thanks a lot for your help, but is there anyway you can
do it "textwise".. My problem is that i have a semicolon
separated file, that kinda looks like this:
hello/tto/t0210/n
that i name *.xls. When opening this file in excel it
separates the text giving me three columns
hello to 210

so i cannot make any setting in excel before i fill the
columns..i basically write the data to a textfile that i
name *.xls. Is there anyway i can write the string so
that it wont see it as a number but rather a string ?

Thank you
/joakim

When you open the file in Excel, doesn't the text import wizard run? This
allows you to specify the separator and designate the fields as
General/Text, etc. - that is, you can 'make settings in Excel before you
fill the columns'. If the wizard doesn't run, try saving your text file as
*.txt rather than *.xls.

 

[joakim]

Thank you!

Problem is that i want to have the string 0210 created in
a Visual Basic application, and written to a *.xls file
saved as the string 0210 instead of an integer...when i
save it as an integer the initial zero is removed in the
column. I cannot make any preseetings in Excel before,
since i simply save the file as text, thats supposed to
be opened by the user when he downloads the file. The
only thing that makes it an excel file is that it is
named *.xls. I havent written anything in excel, i have
merely created a file on the servers hardrive thats for
downloading. So i dont really have anything to do with
excel, other than the fact that i want the file that i
create to open in excel in a satisfying way. So im
wondering how do i save the string 0210 as 0210 in excel.
As it is now, when the users open the file, it says 210,
that is the initial zero is removed. This is because the
column has an integer value.

 

[Joakim]

Thank you!

The textwizard only runs if i open the file from within
excel. If i doubleclick the file in explorer, the wizard
doesnt run, and therefore excel automatically thinks that
0210 is an, or is supposed to be an, integer value.

I can save the files as *.txt but then i must
specifically tell the users to open them from within
excel, otherwise the file will make no sense to them..my
company doesnt want too many support calls, and i can
imagine this giving some calls..its a good idea though,
and Ill pitch it to my boss, but do you know any other
way of doing this.

Thank you
/Joakim

 

[CLR]

210 or 0210 when entered into an Excel cell that has been formatted to
Custom > 0000 will display 0210.......

hth
Vaya con Dios,
Chuck, CABGx3

 

[Joakim]

Yes but the problem is that i cant format it in advance.
What i do is that i write a file on CPU1 in, lets say,
notepad. The file looks like this:
hello\t0210\n
I name this file test.xls, so that it will be recognized
by windows as an excel file. I send this file to another
computer, CPU2. The user at CPU2 receives the file,
doubleclicks it and opens it, without any preformatting
the cells, or setting the Custom->0000...he just opens
the file. The column then displays the "string" 0210 as
an "integer" value 210...which is incorrect. So the
problem here is that i cant change anything in the
settings. I will have to write something in the textfile,
the original textfile (test.xls), so that when it is
opened in excel the column is recognized as a string.

Thanks
/joakim

 

[Myrna Larson]

If you work with a text file, I wouldn't give it an XLS extension. If you are using commas to
separate fields, you can name it with a CSV extension -- that also "belongs" to Excel.

But writing the data with quotes around it does't work: when reading a CSV file, Excel sees the
field as text, so strips the quotes, then "looks at" what's left. 0210 can be interpreted as a
number, so that's what ends up in the cell. To force it to text, you would have to include a
character that isn't a digit or decimal point, maybe an underscore, at the end.

Putting an apostrophe at the beginning of the data, i.e. writing '0210 to the text file also
doesn't work, either. The apostrophe is brought in as a part of the data rather than a flag
character.

There are two other possibilities.

One is an SYLK file. That's also a text file, but in a special format. Problem here is that the
documentation for that format isn't readily available any more. If you create a data file in
Excel, then export it in SYLK format, perhaps you can figure out the codes.

The other is to create a true XLS file in your VB code. To do that, you'll have to look at the
examples of how to control Excel from your VB program. An you'll have to have Excel on your
machine and know enough about Excel and its object model to write the code. AIR there are
examples in the VB6 documentation, but I could be wrong on that.

 

[CLR]

Maybe replace the zeros in your strings with upper case letter O's.......the
strings would "look" similar, but for sure would come through as
text.........then once you were in Excel with strings with leading O's,
maybe you could format and replace the O's with 0's again if you
wanted..........

Vaya con Dios,
Chuck, CABGx3

 

[Joakim]

Brilliant! Thank you

Have a nice day
/Joakim

-----Original Message-----
If you work with a text file, I wouldn't give it an XLS

extension. If you are using commas to

separate fields, you can name it with a CSV extension -- that also "belongs" to Excel.

But writing the data with quotes around it does't work:

when reading a CSV file, Excel sees the

field as text, so strips the quotes, then "looks at"

what's left. 0210 can be interpreted as a

number, so that's what ends up in the cell. To force it

to text, you would have to include a

character that isn't a digit or decimal point, maybe an underscore, at the end.

Putting an apostrophe at the beginning of the data, i.e.

writing '0210 to the text file also

doesn't work, either. The apostrophe is brought in as a

part of the data rather than a flag

character.

There are two other possibilities.

One is an SYLK file. That's also a text file, but in a

special format. Problem here is that the

documentation for that format isn't readily available

any more. If you create a data file in

Excel, then export it in SYLK format, perhaps you can figure out the codes.

The other is to create a true XLS file in your VB code.

To do that, you'll have to look at the

examples of how to control Excel from your VB program.

An you'll have to have Excel on your

machine and know enough about Excel and its object model

to write the code. AIR there are

examples in the VB6 documentation, but I could be wrong on that.

 

[Joakim]

Thank you

Have a nice day
/Joakim

-----Original Message-----
Maybe replace the zeros in your strings with upper case letter O's.......the
strings would "look" similar, but for sure would come through as
text.........then once you were in Excel with strings with leading O's,
maybe you could format and replace the O's with 0's again if you
wanted..........

Vaya con Dios,
Chuck, CABGx3





.