Re: formula application to cells

From: RagDyeR (ragdyer_at_cutoutmsn.com)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 08:22:57 -0800

Even though these characters are probably used as "parts numbers", and
you're probably not intending to use them in any calculations, you could
very easily just format the column into a number format, and *eliminate*
your text formulas altogether.
That is of course, as long as you're *only* using digits.

Try custom formatting your column to :

0000000000

You also mentioned that the column should be zero filled,
so to accomplish these both in one shot:

Select *all* of column A,
<Format> <Cells> <Number> tab,
Click "Custom",
And in the "Type" box enter the ten zeroes,
Then <OK>,
And while the column is *still* selected,
Just type your ten zeroes again,
Then <Ctrl> <Enter>.

All of column A is now filled with zeroes,
And will retain the 10 character, leading zero format. 

-- 
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
Just enter your 10 zeroes into A1,
"C Castle" <crystexas@hotmail.com> wrote in message
news:700b7c47.0403170713.5fecbc83@posting.google.com...
Hello !
I am totally stumped by this one, probably because I'm not an Excel
expert, but logic has served me well thus far....which is why I'm
puzzled by this now....
I am creating an excel *** for my sales guys, they send to me and I
convert to txt file to send to the warehouse.  Each column must be a
certain length, alignment and either zero filled or space filled. ie:
Column A is 10 characters in length, right aligned and zero filled but
that data is not always that many digits, so it must look like this:
0000004587  or 0005896321  or 0000058971 etc.
I found a formula on this board yesterday, allowed the formulas to
recognize labels, and tried putting the formula at the bottom of the
column of data.  It worked!
=IF(LEN(Sheet1!A)<10,CONCATENATE(REPT"0",10-LEN(Sheet1!A)),Sheet1!A),Sheet1!
A)
I copied the formula to bottom of each column, changed the label names
and fill requirements for each, tried it out and it was fine last
night.
Shut down the computer and went to bed, this morning, there are nice
formulas at the bottom of the ***, but they don't work.  Tried
re-typing them, don't work....tried opening a brand new workbook
thinking the alignment of the columns might have cause a
problem...still don't work...
Like I said, this one seems totally illogical to me...got
ideas...other than finding a less taxing job.....?!
Thanks....
CC
Loading