Add spaces for specific fields to paste in

I would like to copy and paste data from Excel into our accounting software
for journal entries. However, I've found that it must equal a specific amount
of spaces in order for it to work. I'd like to create a worksheet for others
to turn in and have a separate tab that will put it in a pastable format. For
instance, the first three fields have the following spaces available: 19, 12
and 6, yet the data to be pasted will most likely be less than that. An
example that works is:

80790-000-000______._____20000-._2252 [period represents the beginning of
the next field and also counts as a space]

The data provided from others would be...
GL: 80790-000-000
Amount: (200.00) [the negative sign goes afterwards and no decimal point is
used]
Job No.: 2252

In the example above, the _ represents spaces that must be put in place in
order for the pasting to line up in the proper fields. Any ideas on how this
could be done? The trick is that not all the required data will be the same.
Amounts will always change and the GL could be just 80790 or 80790-010. If I
could have a formula that counts how many digits are in a cell, then
automatically place spaces to fill up the remaining amount, I believe it
would work.

Thanks for any help.

Corey

I'm confused by your examples, but does this help?

A1 contains
80790-000-000

B1 contains
200

C1 contains
2252

You need to concatenate the 3 cells, with the stipulation that the first
value contains 19 characters, the second contains 12 characters, and the
third contains 6.

Try this in D1:

=A1&REPT(" ",19-LEN(A1))&B1&REPT(" ",12-LEN(B1))&C1&REPT(" ",6-LEN(C1))

Now, to remove the formula from D1, and leave behind only the data with the
appropriate spaces,
Right click in D1 and choose "Copy".
Right click again and choose "Paste Special".
Click on "Values", then <OK>, then <Esc>.

You should now have D1 ready to copy and paste to your accounting software.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Corey" <Corey@discussions.microsoft.com> wrote in message
news:BFBF9ACB-BA10-411C-B42E-5F1DE95B9427@microsoft.com...
>I would like to copy and paste data from Excel into our accounting software
> for journal entries. However, I've found that it must equal a specific
> amount
> of spaces in order for it to work. I'd like to create a worksheet for
> others
> to turn in and have a separate tab that will put it in a pastable format.
> For
> instance, the first three fields have the following spaces available: 19,
> 12
> and 6, yet the data to be pasted will most likely be less than that. An
> example that works is:
>
> 80790-000-000______._____20000-._2252 [period represents the beginning of
> the next field and also counts as a space]
>
> The data provided from others would be...
> GL: 80790-000-000
> Amount: (200.00) [the negative sign goes afterwards and no decimal point
> is
> used]
> Job No.: 2252
>
> In the example above, the _ represents spaces that must be put in place in
> order for the pasting to line up in the proper fields. Any ideas on how
> this
> could be done? The trick is that not all the required data will be the
> same.
> Amounts will always change and the GL could be just 80790 or 80790-010. If
> I
> could have a formula that counts how many digits are in a cell, then
> automatically place spaces to fill up the remaining amount, I believe it
> would work.
>
> Thanks for any help.
>
> Corey

The middle part of this doesn't quite do what the OP asked for. I've
assumed the data is in Sheet1 columns A to C with a header row so the
data starts at A2. Insert a new worksheet and enter the following
formula in A2:

=Sheet1!A2&REPT(" ",19-LEN(Sheet1!A2))

In B2 you should enter the following formula:

=IF(Sheet1!B2<0,REPT("
",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&"-",REPT("
",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&" ")

And in C2 of Sheet 2 you should enter the following formula:

=REPT(" ",6-LEN(Sheet1!C2))&Sheet1!C2

I wasn't sure if you wanted three separate fields, or for them to be
joined together separated by a period. If the latter, you can enter the
following in cell D2:

=A2&"."&B2&"."&C2

Copy these formulae down for as many rows as you have data in Sheet 1.
You can fix the values by selecting all the cells, Click <copy> then
Edit | Paste Special | Values | OK and <esc>, then copy/paste into your
accounting package. Alternatively, you can drag Sheet2 away from the
main window and use File | Save As to give it a different name and a
format to suit your other package, eg csv or dbf, so that you could
then import it.

Hope this helps

Pete

Thanks to both of you. This worked great.

"Pete" wrote:

> The middle part of this doesn't quite do what the OP asked for. I've
> assumed the data is in Sheet1 columns A to C with a header row so the
> data starts at A2. Insert a new worksheet and enter the following
> formula in A2:
>
> =Sheet1!A2&REPT(" ",19-LEN(Sheet1!A2))
>
> In B2 you should enter the following formula:
>
> =IF(Sheet1!B2<0,REPT("
> ",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&"-",REPT("
> ",12-LEN(ABS(Sheet1!B2*100))-1)&ABS(Sheet1!B2*100)&" ")
>
> And in C2 of Sheet 2 you should enter the following formula:
>
> =REPT(" ",6-LEN(Sheet1!C2))&Sheet1!C2
>
> I wasn't sure if you wanted three separate fields, or for them to be
> joined together separated by a period. If the latter, you can enter the
> following in cell D2:
>
> =A2&"."&B2&"."&C2
>
> Copy these formulae down for as many rows as you have data in Sheet 1.
> You can fix the values by selecting all the cells, Click <copy> then
> Edit | Paste Special | Values | OK and <esc>, then copy/paste into your
> accounting package. Alternatively, you can drag Sheet2 away from the
> main window and use File | Save As to give it a different name and a
> format to suit your other package, eg csv or dbf, so that you could
> then import it.
>
> Hope this helps
>
> Pete
>
>