Paste as Text option is missing...help

R

ron

I am using Excel 2000 (9.0.6926 SP-3) and I can't find the "Paste as
Text" option to save my life. It's been a while since I have tried to
use the option, but it should be under "paste special" correct?

Anyone?
 
B

Bob Phillips

Look under PasteSpecial and click the Value option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Gord Dibben

Ron

If you are pasting text from another application, you would hit Paste Special
where you would be given options of "Unicode Text" or "Text"

If pasting from within Excel, follow Bob's advice.

Gord Dibben Excel MVP
 
R

ron

I need to be able to take the value 230.00 and paste it into a cell as
230.00 text. If I paste it as "values", then the pasted cell will
read 230, not 230.00 that I need.

Also, I can format a cell so that typing "519", will display as
"00000519" How can I copy the 00000519 as text into another
cell...keeping all the 0's


Here is why I am asking:

When our company cuts checks, we must submit a text file to the bank
that includes the follwing information:

Account Number, Check Number, Amount, Date

As an example:
Account number 00010433000
Checks - 000519 for 230.00, 000520 for 1,000.00
Date 7/21/04

The txt file that I would submit would look like this:

000104330000005190000000023000702104
000104330000005200000000100000702104

Break up is
00010433000 000519 000000002300 0702104
Account Check# Amount Date

Since the Account number is constant and the date will be the same for
each check run, the only 2 areas of concern are check number and
amount. The idea is to export the check run summary to excel - copy
the numbers, paste them as text, then use the CONCATENATE command to
join all of the text.

Any suggestionas?

TIA Ron
 
D

Dave Peterson

First, I think you've got a mistake in your post.

That 230.00 is missing a trailing 0 (and the date has an extra leading 0).

I think I'd do the special formatting right before I built that long string.
But I think I'd format the cells to match what was required--just so things
matched.

but if you have real numbers in the Account, check#, amount fields and a real
date in the date field, I'd just build my own string:

My formula would look like this with the data in A1:D1,

=TEXT(A1,REPT("0",11)) & TEXT(B1,REPT("0",6))
&TEXT(C1*100,REPT("0",12)) & TEXT(D1,"mmddyy")

I think you'll have to adjust the number of 0's in each field to match your
spec.

Build your working formula and copy it down your helper column.

Then copy that column and paste into Notepad, save your text file from there and
send that.





000104330000005190000000023000702104
Account Check#Amount Date
 
R

ron

Dave:

Thanks for the input. You are correct, I messed up the placement of a
few "0's" as you describe.

While your suggested formula didn't work for me, it did provide the
fundamentals for a solution that will work.

Thanks.

Ron
 
Top