MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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