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

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

(E-Mail Removed) 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

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
>
> (E-Mail Removed) 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

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
>

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

(E-Mail Removed) 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

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).
>
> (E-Mail Removed) 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

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>

(E-Mail Removed) 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).
> >
> > (E-Mail Removed) 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

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