Column length

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ovemal@yahoo.no - 10 Jan 2006 14:01 GMT

Hi
I'm going to fill out an excel sheet that later will bee converted to
other program. To get the converting right each cell needs to have
certain length (same length for the entire column). Like column A
should have 5 characters and column B 15 characters. Some times a cell
will bee empty but it should occupy 5 characters (so column C will
start at character (5+15) no 20). Some columns will bee with numbers
and some will bee with text.

Thanks

Regards
Ove Malde

Reply to this Message

Dave Peterson - 10 Jan 2006 14:56 GMT

You have a few choices (try against a copy of your worksheet):

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/groups?threadm=015b01c32c5f%24b3d398d0%24a501280a%40phx.gbl

> Hi
> I'm going to fill out an excel sheet that later will bee converted to
[quoted text clipped - 9 lines]
> Regards
> Ove Malde

Signature

Dave Peterson

Reply to this Message

ovemal@yahoo.no - 11 Jan 2006 09:12 GMT

Excelent and thanks for your help, I will manage with the code so i
don't have to do it with the vba. Maybe I will try it when I'm done to
see how it works. The file will increase to a huge size but that have
to do.

Thanks

regards
Ove Malde

> You have a few choices (try against a copy of your worksheet):
>
[quoted text clipped - 26 lines]
> > Regards
> > Ove Malde

Reply to this Message

ovemal@yahoo.no - 19 Jan 2006 11:55 GMT

Hi.
I got my file back because they like to have text in the left position
and number values in the rigth position. Is that possible?

thanks in advance

Regards
Ove Malde

> You have a few choices (try against a copy of your worksheet):
>
[quoted text clipped - 12 lines]
> Here's a link that provides a macro:
> http://google.com/groups?threadm=015b01c32c5f%24b3d398d0%24a501280a%40phx.gbl

Reply to this Message

Dave Peterson - 19 Jan 2006 12:31 GMT

That's what this kind of thing did.
=left(a1&rept(" ",5),5)
It makes sure that you get 5 characters (with trailing spaces if necessary).

And
=TEXT(A1,"00000")
would force the numeric value in A1 to be exactly 5 characters--with leading
0's.

=RIGHT(REPT(" ",5)&TEXT(A1,"0"),5)
would replace the leading zeros with leading spaces (up to 5 of them).

> Hi.
> I got my file back because they like to have text in the left position
[quoted text clipped - 21 lines]
> > Here's a link that provides a macro:
> > http://google.com/groups?threadm=015b01c32c5f%24b3d398d0%24a501280a%40phx.gbl

Signature

Dave Peterson

Reply to this Message

ovemal@yahoo.no - 20 Jan 2006 09:40 GMT

thanks for you help and it was this one that did the tric for me with
the numbercells
=RIGHT(REPT(" ",5)&TEXT(A1,"0"),5)
and the left in the text cells.

regards
Ove Malde

> That's what this kind of thing did.
> =left(a1&rept(" ",5),5)
[quoted text clipped - 33 lines]
> > > Here's a link that provides a macro:
> > > http://google.com/groups?threadm=015b01c32c5f%24b3d398d0%24a501280a%40phx.gbl

Reply to this Message

Dave Peterson - 20 Jan 2006 14:36 GMT

Sometimes other programs will accept numbers with leading zeros. If you find
out that other program does that for you, you can make it a little easier to
read by using:

=text(a1,"00000")
or if there's lots of 0's.
=text(a1,rept("0",15))

==
On the other hand, once you get it working, there's no reason to change it.
<vbg>

> thanks for you help and it was this one that did the tric for me with
> the numbercells
[quoted text clipped - 45 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

ovemal@yahoo.no - 23 Jan 2006 13:19 GMT

Thanks.
One thing I had to save the file to a text file, but then excel uses a
lot of tab's between the columns that I had to remove in MS Word. Is
there a way in Excel on saving to textfile without the tabs?

Regards
Ove Malde

Reply to this Message