CONCATENATE with a "0" in front.

[serge]

Hi,
I'm wondering if my CONCATENATE formula could be modified to return a number of 8 digits each time, it need when it is a single digit to return it with a "0" in front.
Thank you.

 

[Fluff]

How about

=TEXT(B3,"00")&TEXT(C3,"00")&TEXT(D3,"00")&TEXT(E3,"00")

 

[serge]

Thank you Fluff it works fine, that was so fast.
I need it as a Number, I add *1 at the end of your formula but it still a text, how can it become a number return ?

 

[Fluff]

How about

=--(TEXT(B3,"00")&TEXT(C3,"00")&TEXT(D3,"00")&TEXT(E3,"00"))

 

[Scott Huish]

=(B3*10^6)+(C3*10000)+(D3*100)+E3

 

[serge]

Thank you so MUCH Fluff and Scott Huish you guys are are so talented I wish I had your knowledge, Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rick Rothstein]

=(B3*10^6)+(C3*10000)+(D3*100)+E3

You could also write that like this...

=SUM(B3:E3*10^{6,4,2,0})

 

[Scott Huish]

You could also write that like this...

=SUM(B3:E3*10^{6,4,2,0})

I like that. Does that work with XL2007, like the OP has or would you need SUMPRODUCT?
=SUMPRODUCT(B3:E3,10^{6,4,2,0})

 

[Rick Rothstein]

I like that. Does that work with XL2007, like the OP has or would you need SUMPRODUCT?
=SUMPRODUCT(B3:E3,10^{6,4,2,0})

Hmm! I must have screwed up! I thought I edited my reply a while ago to add that very notice. Apparently, I forget to physically post it. What I had wanted to post was this...

"I don't have XL2007 so I cannot test the above formula, but the OP might have to array-enter it (using CTRL+SHIFT+ENTER) or he might be able to normally enter it by replacing the SUM function with a SUMPRODUCT function. Again, I cannot test it myself."