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

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

"Art" wrote:

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

="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:

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

It return a formula error on the first "0"

"Marcelo" wrote:

="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:

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

="T" & TEXT(A1,"00000000") & TEXT(A2,"000000000000")
--
Gary''s Student - gsnu200790


"Art" wrote:

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

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

"Gary''s Student" wrote:

="T" & TEXT(A1,"00000000") & TEXT(A2,"000000000000")
--
Gary''s Student - gsnu200790


"Art" wrote:

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

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.
--
Gary''s Student - gsnu200790


"Art" wrote:

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

"Gary''s Student" wrote:

="T" & TEXT(A1,"00000000") & TEXT(A2,"000000000000")
--
Gary''s Student - gsnu200790


"Art" wrote:

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

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

"Art" wrote in message
...
If I use that formula is just displays the formula and doesn't return the
data?

"Gary''s Student" wrote:

="T" & TEXT(A1,"00000000") & TEXT(A2,"000000000000")
--
Gary''s Student - gsnu200790


"Art" wrote:

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

Try

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

edvwvw

Art wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

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

"edvwvw via OfficeKB.com" wrote:

Try

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

edvwvw

Art wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

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

Cliff Edwards

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

Cliff Edwards

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,"00 0000000000")

This is probably what you were trying to tell me.

Thanks Again

"Art" wrote:

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

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

=concatenate("T",TEXT(A1,"000000000"))&TEXT(A2,"00 0000000000") could be
expressed as
=concatenate("T",TEXT(A1,"000000000"),TEXT(A2,"000 000000000")) or
="T"&TEXT(A1,"000000000")&TEXT(A2,"000000000000 ")
--
David Biddulph

"Art" wrote in message
...
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,"00 0000000000")

This is probably what you were trying to tell me.

Thanks Again

"Art" wrote:

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