Formula Question ??
Posted by Herman on May 02, 2001 9:49 PM
I have three cells a1 a2 and a3
in these cells i have numbers with the format 00
so when i type in 2 in the cell stands 02
typed in a1 2 looks in the cell as 02
a2 3 03
a3 1 01
now i want to display in cell a4 the result of combining the cells a1 a2 and a3
so there should be 020301 in cell a4 but i get 231
how can i fix this problem it seems that what is visible on my screen is not what is being combined together.
thanx
Posted by Mark W. on May 02, 2001 10:27 PM
=0&SUMPRODUCT(A1:C1,{10000,100,1})
Posted by Mark W. on May 02, 2001 10:29 PM
Oops! Getting late...
=0&SUMPRODUCT(A1:A3,{10000;100;1})
Posted by herman on May 02, 2001 10:45 PM
Re: Oops! Getting late...
it won't work for me i want to combine the values as text, so when i type in 1 in the cell stands 01
and what in the cell stands that is what i want and not the 1 which i still get
Posted by Mark W. on May 02, 2001 10:55 PM
Re: Oops! Getting late...
Herman, I'm not quite sure what you mean!
=0&SUMPRODUCT(A1:A3,{10000;100;1}) does exactly
what you requested when A1:A3 contains {2;3;1}.
Don't get confused by cell formatting. Formulas
don't use the formatted values, but rather the
onesstored internally and viewable in the formula
bar.
Here's what my formula does:
1. Multiples A1 by 10000 producing 20000
2. Multiples A2 by 100 producting 300
3. Multiples A3 by 1 producing 1
4. Sums these values producing 20301
4. Concatenates a leading 0 producing the
text string, "020301"
Believe me -- it works!!!
Posted by Dave Hawley on May 02, 2001 10:58 PM
Re: Oops! Getting late...
Hi Herman
Using your example, this formula will give you the text string:
010203
=TEXT(A1,"0#")&TEXT(A2,"0#")&TEXT(A3,"0#")
Dave
OzGrid Business Applications
Posted by Mark W. on May 02, 2001 11:02 PM
And...
...mine does it all with only 1 function call and
1 operator... Herman, I'm not quite sure what you mean! =0&SUMPRODUCT(A1:A3,{10000;100;1}) does exactly
