Concatenate 2 different number formats

[Art]

I am trying to create a string of numbers from 2 different number formats.
The formula I am trying is on test tab 3 =concatenate("T",Test1!A1,Test1!A2).
I have created custom format 00000000 for A1 and custom format 000000000000
for A2.
A1 has 000000006
A2 has 000001212422

I would like it to return T000000006000001212422 but it returns T61212422.
Any ideas on how to make it work?

Thanks

 

[Dennis]

Do A1 and A2 need to be numbers ?
If you enter them with an apostrophe to signify text, it works OK
'000000006
'000001212422
If you need them as numbers you can use the =VALUE(A1) to get the number

 

[Marcelo]

="T"&rept("0",8-len(a1))&a1&rept("0",12-len(a2))&a2


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Art" escreveu:

 

[Gary''s Student]

="T" & TEXT(A1,"00000000") & TEXT(A2,"000000000000")

 

[edvwvw via OfficeKB.com]

Try

="T"&TEXT(A1,"00000000")&TEXT(A2,"000000000000")

edvwvw

 

[Art]

If I use that formula is just displays the formula and doesn't return the data?

 

[ward376]

="T"&TEXT(Test!A1,"000000000")&TEXT(Test!B1,"000000000000")

Cliff Edwards

 

[Gary''s Student]

Make sure that when you paste the formula in, no extra spaces are included in
the paste.

Better to copy from the post and paste directly in the formula bar instead
of the cell.

 

[Art]

This also returns the formula and not data. A1 and A2 are forumulas. They sum
different things if that matters?

 

[ward376]

Make sure that the cell isn't formatted as text - prior to entering
the formula.

Cliff Edwards

 

[Art]

It return a formula error on the first "0"

="T"&rept("0",8-len(a1))&a1&rept("0",12-len(a2))&a2


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Art" escreveu:

 

[David Biddulph]

You've probably got the cell formatted as text. Reformat it as General, and
re-enter the formula.

 

[Art]

Thank you all for the rapid responses. This is the formula that I was able to
get to work. =concatenate("T",TEXT(A1,"000000000"))&TEXT(A2,"000000000000")

This is probably what you were trying to tell me.

Thanks Again

 

[David Biddulph]

You've chosen an interesting combination of the CONCATENATE function and the
& operator.

=concatenate("T",TEXT(A1,"000000000"))&TEXT(A2,"000000000000") could be
expressed as
=concatenate("T",TEXT(A1,"000000000"),TEXT(A2,"000000000000")) or
="T"&TEXT(A1,"000000000")&TEXT(A2,"000000000000")