Column length

[[email protected]]

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

[Dave Peterson]

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=015...280a%40phx.gbl

[email protected] wrote:
>
> 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

--

Dave Peterson

[[email protected]]

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


Dave Peterson wrote:
> 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=015...280a%40phx.gbl
>
> [email protected] wrote:
> >
> > 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
>
> --
>
> Dave Peterson

[[email protected]]

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




Dave Peterson wrote:
> 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=015...280a%40phx.gbl
>

[Dave Peterson]

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

[email protected] wrote:
>
> 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
>
> Dave Peterson wrote:
> > 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=015...280a%40phx.gbl
> >

--

Dave Peterson

[[email protected]]

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



Dave Peterson wrote:
> 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).
>
> [email protected] wrote:
> >
> > 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
> >
> > Dave Peterson wrote:
> > > 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=015...280a%40phx.gbl
> > >
>
> --
>
> Dave Peterson

[Dave Peterson]

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>

[email protected] wrote:
>
> 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
>
> Dave Peterson wrote:
> > 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).
> >
> > [email protected] wrote:
> > >
> > > 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
> > >
> > > Dave Peterson wrote:
> > > > 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=015...280a%40phx.gbl
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[[email protected]]

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