Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
="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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
="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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
="T"&TEXT(Test!A1,"000000000")&TEXT(Test!B1,"00000 0000000")
Cliff Edwards |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
Make sure that the cell isn't formatted as text - prior to entering
the formula. Cliff Edwards |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats-SOLVED
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate 2 different number formats-SOLVED
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get different text formats in a concatenate function | Excel Discussion (Misc queries) | |||
number formats after concatenate | Excel Worksheet Functions | |||
number formats after concatenate | Excel Worksheet Functions | |||
How to Concatenate with a text & number | Excel Worksheet Functions | |||
concatenate number format issue | Excel Worksheet Functions |