# Ruby and Excel problem (formatting)

**URL:** <https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620>\
**Category:** Rails\
**Created:** [December 2, 2005, 3:43pm UTC](https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620 "2005-12-02T15:43:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![brutyn\_nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e79b87/32.png) [@brutyn\_nick](https://www.ruby-forum.com/u/brutyn_nick)\
**Post date:** [December 2, 2005, 3:43pm UTC](https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620/1 "2005-12-02T15:43:19Z")

</div>

hey, i create an excel file just be putting a simple rhtml to the  
browser with  
@headers[“Content-type”] = “application/vnd.ms-excel” but i have a small  
problem.

i also put pinnumbers in the excel file like this:

Lastname Firstname Phone Phone pin\<% for employee in @employees %\>  
\<%= employee.last\_name %\> \<%= employee.first\_name %\> \<%= employee.phone  
%\> \<%=  
employee.phone\_pin.rjust(4,“0”) %\>\<% end %\>

Lastname Firstname Phone Phone pin  
Brutyn A. xxxxxxxxxx 21  
Brutyn G. xxxxxxxxxx 6641  
Brutyn L. xxxxxxxxxx 1237  
Brutyn N. xxxxxxxxxx 1134

this is my problem 21 should be 0021

i also have tried to\_s

and '\<%= employee.phone\_pin.rjust(4,“0”) %\>  
but then i get '0021 and '6641  
which is not beautiful

can anyone help me with some cell format or something or some handy  
trick

thx in advance

---

<div class="post-metadata">

**Author:** ![brutyn\_nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e79b87/32.png) [@brutyn\_nick](https://www.ruby-forum.com/u/brutyn_nick)\
**Post date:** [December 2, 2005, 4:03pm UTC](https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620/2 "2005-12-02T16:03:41Z")

</div>

Brutyn nick wrote:

> Brutyn N. xxxxxxxxxx 1134
> 
> this is my problem 21 should be 0021

My hope is that someone out there will contradict me, but everything  
I’ve seen says that if you open a plain text file in Excel and there’s a  
column that contains only numbers, the values in that column will come  
into Excel with all leading zeros stripped. And once it’s in Excel  
there’s no way to get the leading zeros back, without turning the file  
into a native Excel document and setting a custom number format on that  
column/cell. Simply changing the column to be “Text”, which is supposed  
to leave the data as entered, doesn’t bring back the leading zeros.

Again, I’m hoping that someone can tell me I’m wrong, but this is  
something I’ve been wrestling with for years with Excel and I’ve never  
seen a solution…

-Brian

---

<div class="post-metadata">

**Author:** ![brutyn\_nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e79b87/32.png) [@brutyn\_nick](https://www.ruby-forum.com/u/brutyn_nick)\
**Post date:** [December 2, 2005, 5:37pm UTC](https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620/3 "2005-12-02T17:37:08Z")

</div>

Brian V. Hughes \<brianvh@…\> writes:

> something I’ve been wrestling with for years with Excel and I’ve never  
> seen a solution…
> 
> -Brian

Brian,

When importing a text file into excel there is a panel in the import  
wizard  
that lets you set the column type. Changing it from ‘general’ to ‘text’  
will  
stop the suppression of the leading zeroes, but your column type will be  
a text  
type, not a numerirc type.

You can click on the format/cell option in the tool bar, click number  
and fill  
in the number of zeroes you want the width of your cell to be. This will  
keep  
the number, and even zeroes will be just as wide as any other numbers.

There is also a trick with concatenate and RIGHT in a formula, but I  
don’t have  
a copy of excel or windows, so I can’t remember how to do that.

It has been many years since I worked with excel, but you can probably  
set  
these attributes with vba if you are importing with a vba script

---

<div class="post-metadata">

**Author:** ![brutyn\_nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e79b87/32.png) [@brutyn\_nick](https://www.ruby-forum.com/u/brutyn_nick)\
**Post date:** [December 2, 2005, 6:01pm UTC](https://www.ruby-forum.com/t/ruby-and-excel-problem-formatting/56620/4 "2005-12-02T18:01:28Z")

</div>

> Again, I’m hoping that someone can tell me I’m wrong, but this is  
> something I’ve been wrestling with for years with Excel and I’ve never  
> seen a solution…

i found a solution

=TEKST(\<%= employee.phone\_pin %\>;“0000”)

then i get 0021

for u engligh guys tekst() =\> text() i think

thanks by the way