Hi to all.

I need some help with exporting excel data to text file, I have data in
cells, I have a code for only returning max of 12 characters, this is fine
and working, but the cells that have below 12 characters I need these cells
to padded out with spaces so that when I export to a text file it looks like
columns in the text file.

Can anyone help me with this

Regards

myString = Left(myString & Space(12), 12)

"santaviga" <(E-Mail Removed)> skrev i meddelelsen
news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> Hi to all.
>
> I need some help with exporting excel data to text file, I have data in
> cells, I have a code for only returning max of 12 characters, this is fine
> and working, but the cells that have below 12 characters I need these
> cells
> to padded out with spaces so that when I export to a text file it looks
> like
> columns in the text file.
>
> Can anyone help me with this
>
> Regards

How do I input this into a cell to operate???

Thanks

"Lazzzx" wrote:

> myString = Left(myString & Space(12), 12)
>
> "santaviga" <(E-Mail Removed)> skrev i meddelelsen
> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> > Hi to all.
> >
> > I need some help with exporting excel data to text file, I have data in
> > cells, I have a code for only returning max of 12 characters, this is fine
> > and working, but the cells that have below 12 characters I need these
> > cells
> > to padded out with spaces so that when I export to a text file it looks
> > like
> > columns in the text file.
> >
> > Can anyone help me with this
> >
> > Regards
>
>

In the macro code (you are using a macro, right?) that is now returning your
max of 12 characters, replace whatever is doing the truncating (it should be
using the Left function) with the code line Lazzzx gave you (substituting
the variable name or cell value reference used in your current formula for
the myString example variable Lazzzx gave you; assigning it to whatever
variable you are currently assigning it to).

Usually if you are going to refer to code (or a formula on the spreadsheet
side of things), it is a good idea to post that code (or formula) rather
than make an off-hand reference to it; that way, we can post the
modification to it instead of giving a general example for you to try and
implement.

Rick


"santaviga" <(E-Mail Removed)> wrote in message
news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
> How do I input this into a cell to operate???
>
> Thanks
>
> "Lazzzx" wrote:
>
>> myString = Left(myString & Space(12), 12)
>>
>> "santaviga" <(E-Mail Removed)> skrev i meddelelsen
>> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
>> > Hi to all.
>> >
>> > I need some help with exporting excel data to text file, I have data in
>> > cells, I have a code for only returning max of 12 characters, this is
>> > fine
>> > and working, but the cells that have below 12 characters I need these
>> > cells
>> > to padded out with spaces so that when I export to a text file it looks
>> > like
>> > columns in the text file.
>> >
>> > Can anyone help me with this
>> >
>> > Regards
>>
>>

I am not using a macro, my company doesn't allow them, is there any other way
to do this??

"Rick Rothstein (MVP - VB)" wrote:

> In the macro code (you are using a macro, right?) that is now returning your
> max of 12 characters, replace whatever is doing the truncating (it should be
> using the Left function) with the code line Lazzzx gave you (substituting
> the variable name or cell value reference used in your current formula for
> the myString example variable Lazzzx gave you; assigning it to whatever
> variable you are currently assigning it to).
>
> Usually if you are going to refer to code (or a formula on the spreadsheet
> side of things), it is a good idea to post that code (or formula) rather
> than make an off-hand reference to it; that way, we can post the
> modification to it instead of giving a general example for you to try and
> implement.
>
> Rick
>
>
> "santaviga" <(E-Mail Removed)> wrote in message
> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
> > How do I input this into a cell to operate???
> >
> > Thanks
> >
> > "Lazzzx" wrote:
> >
> >> myString = Left(myString & Space(12), 12)
> >>
> >> "santaviga" <(E-Mail Removed)> skrev i meddelelsen
> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> >> > Hi to all.
> >> >
> >> > I need some help with exporting excel data to text file, I have data in
> >> > cells, I have a code for only returning max of 12 characters, this is
> >> > fine
> >> > and working, but the cells that have below 12 characters I need these
> >> > cells
> >> > to padded out with spaces so that when I export to a text file it looks
> >> > like
> >> > columns in the text file.
> >> >
> >> > Can anyone help me with this
> >> >
> >> > Regards
> >>
> >>
>
>

I figured you were using a macro because you said you have "code" (a
formula, assuming that is what you meant, is not code) and you posted your
question in a programming newsgroup. Anyway, referencing my last paragraph
for emphasis... how are you now returning your maximum of 12 characters
(that is, what is the "code" or formula being used)?

Rick


"santaviga" <(E-Mail Removed)> wrote in message
news:7F6CA61C-606A-4651-BCC6-(E-Mail Removed)...
>I am not using a macro, my company doesn't allow them, is there any other
>way
> to do this??
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> In the macro code (you are using a macro, right?) that is now returning
>> your
>> max of 12 characters, replace whatever is doing the truncating (it should
>> be
>> using the Left function) with the code line Lazzzx gave you (substituting
>> the variable name or cell value reference used in your current formula
>> for
>> the myString example variable Lazzzx gave you; assigning it to whatever
>> variable you are currently assigning it to).
>>
>> Usually if you are going to refer to code (or a formula on the
>> spreadsheet
>> side of things), it is a good idea to post that code (or formula) rather
>> than make an off-hand reference to it; that way, we can post the
>> modification to it instead of giving a general example for you to try and
>> implement.
>>
>> Rick
>>
>>
>> "santaviga" <(E-Mail Removed)> wrote in message
>> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
>> > How do I input this into a cell to operate???
>> >
>> > Thanks
>> >
>> > "Lazzzx" wrote:
>> >
>> >> myString = Left(myString & Space(12), 12)
>> >>
>> >> "santaviga" <(E-Mail Removed)> skrev i meddelelsen
>> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
>> >> > Hi to all.
>> >> >
>> >> > I need some help with exporting excel data to text file, I have data
>> >> > in
>> >> > cells, I have a code for only returning max of 12 characters, this
>> >> > is
>> >> > fine
>> >> > and working, but the cells that have below 12 characters I need
>> >> > these
>> >> > cells
>> >> > to padded out with spaces so that when I export to a text file it
>> >> > looks
>> >> > like
>> >> > columns in the text file.
>> >> >
>> >> > Can anyone help me with this
>> >> >
>> >> > Regards
>> >>
>> >>
>>
>>

Hi Rick,

Sorry for the misunderstanding. The formula i'm using is the following:

=LEFT(Sheet1!A1,12)

This returns the text in that cell to a max of 12, but I need this to pan
out to 12 characters if this cell only contains 5 characters so it will be
placed in a text file as if in colums.

Regards

"Rick Rothstein (MVP - VB)" wrote:

> I figured you were using a macro because you said you have "code" (a
> formula, assuming that is what you meant, is not code) and you posted your
> question in a programming newsgroup. Anyway, referencing my last paragraph
> for emphasis... how are you now returning your maximum of 12 characters
> (that is, what is the "code" or formula being used)?
>
> Rick
>
>
> "santaviga" <(E-Mail Removed)> wrote in message
> news:7F6CA61C-606A-4651-BCC6-(E-Mail Removed)...
> >I am not using a macro, my company doesn't allow them, is there any other
> >way
> > to do this??
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> In the macro code (you are using a macro, right?) that is now returning
> >> your
> >> max of 12 characters, replace whatever is doing the truncating (it should
> >> be
> >> using the Left function) with the code line Lazzzx gave you (substituting
> >> the variable name or cell value reference used in your current formula
> >> for
> >> the myString example variable Lazzzx gave you; assigning it to whatever
> >> variable you are currently assigning it to).
> >>
> >> Usually if you are going to refer to code (or a formula on the
> >> spreadsheet
> >> side of things), it is a good idea to post that code (or formula) rather
> >> than make an off-hand reference to it; that way, we can post the
> >> modification to it instead of giving a general example for you to try and
> >> implement.
> >>
> >> Rick
> >>
> >>
> >> "santaviga" <(E-Mail Removed)> wrote in message
> >> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
> >> > How do I input this into a cell to operate???
> >> >
> >> > Thanks
> >> >
> >> > "Lazzzx" wrote:
> >> >
> >> >> myString = Left(myString & Space(12), 12)
> >> >>
> >> >> "santaviga" <(E-Mail Removed)> skrev i meddelelsen
> >> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> >> >> > Hi to all.
> >> >> >
> >> >> > I need some help with exporting excel data to text file, I have data
> >> >> > in
> >> >> > cells, I have a code for only returning max of 12 characters, this
> >> >> > is
> >> >> > fine
> >> >> > and working, but the cells that have below 12 characters I need
> >> >> > these
> >> >> > cells
> >> >> > to padded out with spaces so that when I export to a text file it
> >> >> > looks
> >> >> > like
> >> >> > columns in the text file.
> >> >> >
> >> >> > Can anyone help me with this
> >> >> >
> >> >> > Regards
> >> >>
> >> >>
> >>
> >>
>
>

Assuming you want the padding on the right, try using this instead...

=LEFT(A1&REPT(" ",12),12)

If you want the padding on the left (as maybe for numbers), then you would
use this formula...

=RIGHT(REPT(" ",12)&A1,12)

Rick


"santaviga" <(E-Mail Removed)> wrote in message
news:1DFFBF73-1636-4DAD-992B-(E-Mail Removed)...
> Hi Rick,
>
> Sorry for the misunderstanding. The formula i'm using is the following:
>
> =LEFT(Sheet1!A1,12)
>
> This returns the text in that cell to a max of 12, but I need this to pan
> out to 12 characters if this cell only contains 5 characters so it will be
> placed in a text file as if in colums.
>
> Regards
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> I figured you were using a macro because you said you have "code" (a
>> formula, assuming that is what you meant, is not code) and you posted
>> your
>> question in a programming newsgroup. Anyway, referencing my last
>> paragraph
>> for emphasis... how are you now returning your maximum of 12 characters
>> (that is, what is the "code" or formula being used)?
>>
>> Rick
>>
>>
>> "santaviga" <(E-Mail Removed)> wrote in message
>> news:7F6CA61C-606A-4651-BCC6-(E-Mail Removed)...
>> >I am not using a macro, my company doesn't allow them, is there any
>> >other
>> >way
>> > to do this??
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >
>> >> In the macro code (you are using a macro, right?) that is now
>> >> returning
>> >> your
>> >> max of 12 characters, replace whatever is doing the truncating (it
>> >> should
>> >> be
>> >> using the Left function) with the code line Lazzzx gave you
>> >> (substituting
>> >> the variable name or cell value reference used in your current formula
>> >> for
>> >> the myString example variable Lazzzx gave you; assigning it to
>> >> whatever
>> >> variable you are currently assigning it to).
>> >>
>> >> Usually if you are going to refer to code (or a formula on the
>> >> spreadsheet
>> >> side of things), it is a good idea to post that code (or formula)
>> >> rather
>> >> than make an off-hand reference to it; that way, we can post the
>> >> modification to it instead of giving a general example for you to try
>> >> and
>> >> implement.
>> >>
>> >> Rick
>> >>
>> >>
>> >> "santaviga" <(E-Mail Removed)> wrote in message
>> >> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
>> >> > How do I input this into a cell to operate???
>> >> >
>> >> > Thanks
>> >> >
>> >> > "Lazzzx" wrote:
>> >> >
>> >> >> myString = Left(myString & Space(12), 12)
>> >> >>
>> >> >> "santaviga" <(E-Mail Removed)> skrev i
>> >> >> meddelelsen
>> >> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
>> >> >> > Hi to all.
>> >> >> >
>> >> >> > I need some help with exporting excel data to text file, I have
>> >> >> > data
>> >> >> > in
>> >> >> > cells, I have a code for only returning max of 12 characters,
>> >> >> > this
>> >> >> > is
>> >> >> > fine
>> >> >> > and working, but the cells that have below 12 characters I need
>> >> >> > these
>> >> >> > cells
>> >> >> > to padded out with spaces so that when I export to a text file it
>> >> >> > looks
>> >> >> > like
>> >> >> > columns in the text file.
>> >> >> >
>> >> >> > Can anyone help me with this
>> >> >> >
>> >> >> > Regards
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>

thanks a lot Rick, works a treat.

Regards

"Rick Rothstein (MVP - VB)" wrote:

> Assuming you want the padding on the right, try using this instead...
>
> =LEFT(A1&REPT(" ",12),12)
>
> If you want the padding on the left (as maybe for numbers), then you would
> use this formula...
>
> =RIGHT(REPT(" ",12)&A1,12)
>
> Rick
>
>
> "santaviga" <(E-Mail Removed)> wrote in message
> news:1DFFBF73-1636-4DAD-992B-(E-Mail Removed)...
> > Hi Rick,
> >
> > Sorry for the misunderstanding. The formula i'm using is the following:
> >
> > =LEFT(Sheet1!A1,12)
> >
> > This returns the text in that cell to a max of 12, but I need this to pan
> > out to 12 characters if this cell only contains 5 characters so it will be
> > placed in a text file as if in colums.
> >
> > Regards
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> I figured you were using a macro because you said you have "code" (a
> >> formula, assuming that is what you meant, is not code) and you posted
> >> your
> >> question in a programming newsgroup. Anyway, referencing my last
> >> paragraph
> >> for emphasis... how are you now returning your maximum of 12 characters
> >> (that is, what is the "code" or formula being used)?
> >>
> >> Rick
> >>
> >>
> >> "santaviga" <(E-Mail Removed)> wrote in message
> >> news:7F6CA61C-606A-4651-BCC6-(E-Mail Removed)...
> >> >I am not using a macro, my company doesn't allow them, is there any
> >> >other
> >> >way
> >> > to do this??
> >> >
> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >
> >> >> In the macro code (you are using a macro, right?) that is now
> >> >> returning
> >> >> your
> >> >> max of 12 characters, replace whatever is doing the truncating (it
> >> >> should
> >> >> be
> >> >> using the Left function) with the code line Lazzzx gave you
> >> >> (substituting
> >> >> the variable name or cell value reference used in your current formula
> >> >> for
> >> >> the myString example variable Lazzzx gave you; assigning it to
> >> >> whatever
> >> >> variable you are currently assigning it to).
> >> >>
> >> >> Usually if you are going to refer to code (or a formula on the
> >> >> spreadsheet
> >> >> side of things), it is a good idea to post that code (or formula)
> >> >> rather
> >> >> than make an off-hand reference to it; that way, we can post the
> >> >> modification to it instead of giving a general example for you to try
> >> >> and
> >> >> implement.
> >> >>
> >> >> Rick
> >> >>
> >> >>
> >> >> "santaviga" <(E-Mail Removed)> wrote in message
> >> >> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
> >> >> > How do I input this into a cell to operate???
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > "Lazzzx" wrote:
> >> >> >
> >> >> >> myString = Left(myString & Space(12), 12)
> >> >> >>
> >> >> >> "santaviga" <(E-Mail Removed)> skrev i
> >> >> >> meddelelsen
> >> >> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> >> >> >> > Hi to all.
> >> >> >> >
> >> >> >> > I need some help with exporting excel data to text file, I have
> >> >> >> > data
> >> >> >> > in
> >> >> >> > cells, I have a code for only returning max of 12 characters,
> >> >> >> > this
> >> >> >> > is
> >> >> >> > fine
> >> >> >> > and working, but the cells that have below 12 characters I need
> >> >> >> > these
> >> >> >> > cells
> >> >> >> > to padded out with spaces so that when I export to a text file it
> >> >> >> > looks
> >> >> >> > like
> >> >> >> > columns in the text file.
> >> >> >> >
> >> >> >> > Can anyone help me with this
> >> >> >> >
> >> >> >> > Regards
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

Hi Rick,

If I were to put this into a macro so it will do what i require with that,
how would the code be written. Any ideas.

Regards

"Rick Rothstein (MVP - VB)" wrote:

> Assuming you want the padding on the right, try using this instead...
>
> =LEFT(A1&REPT(" ",12),12)
>
> If you want the padding on the left (as maybe for numbers), then you would
> use this formula...
>
> =RIGHT(REPT(" ",12)&A1,12)
>
> Rick
>
>
> "santaviga" <(E-Mail Removed)> wrote in message
> news:1DFFBF73-1636-4DAD-992B-(E-Mail Removed)...
> > Hi Rick,
> >
> > Sorry for the misunderstanding. The formula i'm using is the following:
> >
> > =LEFT(Sheet1!A1,12)
> >
> > This returns the text in that cell to a max of 12, but I need this to pan
> > out to 12 characters if this cell only contains 5 characters so it will be
> > placed in a text file as if in colums.
> >
> > Regards
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> I figured you were using a macro because you said you have "code" (a
> >> formula, assuming that is what you meant, is not code) and you posted
> >> your
> >> question in a programming newsgroup. Anyway, referencing my last
> >> paragraph
> >> for emphasis... how are you now returning your maximum of 12 characters
> >> (that is, what is the "code" or formula being used)?
> >>
> >> Rick
> >>
> >>
> >> "santaviga" <(E-Mail Removed)> wrote in message
> >> news:7F6CA61C-606A-4651-BCC6-(E-Mail Removed)...
> >> >I am not using a macro, my company doesn't allow them, is there any
> >> >other
> >> >way
> >> > to do this??
> >> >
> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >
> >> >> In the macro code (you are using a macro, right?) that is now
> >> >> returning
> >> >> your
> >> >> max of 12 characters, replace whatever is doing the truncating (it
> >> >> should
> >> >> be
> >> >> using the Left function) with the code line Lazzzx gave you
> >> >> (substituting
> >> >> the variable name or cell value reference used in your current formula
> >> >> for
> >> >> the myString example variable Lazzzx gave you; assigning it to
> >> >> whatever
> >> >> variable you are currently assigning it to).
> >> >>
> >> >> Usually if you are going to refer to code (or a formula on the
> >> >> spreadsheet
> >> >> side of things), it is a good idea to post that code (or formula)
> >> >> rather
> >> >> than make an off-hand reference to it; that way, we can post the
> >> >> modification to it instead of giving a general example for you to try
> >> >> and
> >> >> implement.
> >> >>
> >> >> Rick
> >> >>
> >> >>
> >> >> "santaviga" <(E-Mail Removed)> wrote in message
> >> >> news:5231C89F-D3F4-4997-A65D-(E-Mail Removed)...
> >> >> > How do I input this into a cell to operate???
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > "Lazzzx" wrote:
> >> >> >
> >> >> >> myString = Left(myString & Space(12), 12)
> >> >> >>
> >> >> >> "santaviga" <(E-Mail Removed)> skrev i
> >> >> >> meddelelsen
> >> >> >> news:76D2333F-7B72-4ACD-8D43-(E-Mail Removed)...
> >> >> >> > Hi to all.
> >> >> >> >
> >> >> >> > I need some help with exporting excel data to text file, I have
> >> >> >> > data
> >> >> >> > in
> >> >> >> > cells, I have a code for only returning max of 12 characters,
> >> >> >> > this
> >> >> >> > is
> >> >> >> > fine
> >> >> >> > and working, but the cells that have below 12 characters I need
> >> >> >> > these
> >> >> >> > cells
> >> >> >> > to padded out with spaces so that when I export to a text file it
> >> >> >> > looks
> >> >> >> > like
> >> >> >> > columns in the text file.
> >> >> >> >
> >> >> >> > Can anyone help me with this
> >> >> >> >
> >> >> >> > Regards
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>