software

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:

=A1amp;REPT(quot; quot;,19-LEN(A1))amp;B1amp;REPT(quot; quot;,12-LEN(B1))amp;C1amp;REPT(quot; quot;,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 quot;Copyquot;.
Right click again and choose quot;Paste Specialquot;.
Click on quot;Valuesquot;, then lt;OKgt;, then lt;Escgt;.

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 !
---------------------------------------------------------------------------

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

In B2 you should enter the following formula:

=IF(Sheet1!B2lt;0,REPT(quot;
quot;,12-LEN(ABS(Sheet1!B2*100))-1)amp;ABS(Sheet1!B2*100)amp;quot;-quot;,REPT(quot;
quot;,12-LEN(ABS(Sheet1!B2*100))-1)amp;ABS(Sheet1!B2*100)amp;quot; quot;)

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

=REPT(quot; quot;,6-LEN(Sheet1!C2))amp;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:

=A2amp;quot;.quot;amp;B2amp;quot;.quot;amp;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 lt;copygt; then
Edit | Paste Special | Values | OK and lt;escgt;, 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

PeteThanks to both of you. This worked great.

quot;Petequot; wrote:

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