formula application to cells

  • Thread starter Thread starter C Castle
  • Start date Start date
C

C Castle

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 sheet 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 sheet, 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
 
Hi
what results do you get? What is 'do not work'?
As far as I can see these formulas shouldn't have worked yesterday
(wrong cell references). Try the following in column B, row 1 (cell B1)
if your data is in column A
=IF(LEN(A1)<10,REPT("0",10-LEN(A1)) & A1,A1)

and copy down
 
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,



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 sheet 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 sheet, 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
 
Frank -

The results that I get are only just that the formulas sit there in
the field taking up space.

I tried the formula that you gave and it brings the information typed
into A1 into proper format in field B1. I am guessing that the idea
is to insert this formula into an "extra" column all the way down,
then "hide the column" so that it's not confusing to the sales agent.

If there is a way to apply this formula to the column as a whole
without doing what I said in the previous paragraph can you let me
know.

Thanks for your help..!

CC
 
Hi
try the following
- insert the formula in row 1 of a helper column
- just copy it down or drag it down for all rows

if you want to get rid of the formulas try
- select this helper column and copy it (e.g. with CTRL+C)
- goto 'Edit Üaste Special' and choose 'Values'

now yoo can delete the old column
 
Yes...I did this for the numerical columns, but many of the columns
ie: First name, Last name...etc have to be left aligned and space
filled. I tried to use the same idea, custome format and then hitting
the space bar insted of "0"'s, but it does not work. So that's why I
was turning to formulas or functions....still working on it, but thank
you so much for your response.

CC
 
Back
Top