Concatenation help
in [Functions]
|
From: Nadine on 4 Jun 2010 10:00 Gary, If the payment # is 10, this formula records it as 01. How do I write the formula so pmt 1 comes out as 01 and pmt 10 comes out as 10 and pmt 15 comes out as 15, etc? Thanks. "Gary''s Student" wrote: > A tiny trick: > > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00") > -- > Gary''s Student - gsnu201003 > > > "Nadine" wrote: > > > I have 3 cells I need to merge into one. One of the cells is a number > > anywhere from 1-16 or more. What I need is for the number in this cells to > > always be concatenated into a 2 digit number. Example: > > Cell A1 = USA123-4555678952 > > Cell A2 = CD > > Cell A3 = 4 > > I need the final output to be 234555678952CD04. > > So far I have the following formula: > > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&A3 but this doesn't put "04" after the > > CD. It only puts "4" after it. Any ideas for Excel 2003? > > Thanks.
From: steve on 4 Jun 2010 10:35 Change the &TEXT(A3,"00") to read &TEXT(A3,"0#") Regards Steve "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message news:15B0073E-E980-4DD9-BADD-BE2B4A174BD4(a)microsoft.com... > Gary, > If the payment # is 10, this formula records it as 01. How do I write the > formula so pmt 1 comes out as 01 and pmt 10 comes out as 10 and pmt 15 > comes > out as 15, etc? > Thanks. > > > "Gary''s Student" wrote: > >> A tiny trick: >> >> =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00") >> -- >> Gary''s Student - gsnu201003 >> >> >> "Nadine" wrote: >> >> > I have 3 cells I need to merge into one. One of the cells is a number >> > anywhere from 1-16 or more. What I need is for the number in this >> > cells to >> > always be concatenated into a 2 digit number. Example: >> > Cell A1 = USA123-4555678952 >> > Cell A2 = CD >> > Cell A3 = 4 >> > I need the final output to be 234555678952CD04. >> > So far I have the following formula: >> > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&A3 but this doesn't put "04" >> > after the >> > CD. It only puts "4" after it. Any ideas for Excel 2003? >> > Thanks.
From: Jackpot on 4 Jun 2010 11:12 Which cell contain pmt number... =TEXT(A1,"00") will display the numeric in A1 as 2 digits (zero padded). If this is nothing to do with the previous formula and in a totally differnet cell; then change the number format of the cell to 00 "Nadine" wrote: > Gary, > If the payment # is 10, this formula records it as 01. How do I write the > formula so pmt 1 comes out as 01 and pmt 10 comes out as 10 and pmt 15 comes > out as 15, etc? > Thanks. > > > "Gary''s Student" wrote: > > > A tiny trick: > > > > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00") > > -- > > Gary''s Student - gsnu201003 > > > > > > "Nadine" wrote: > > > > > I have 3 cells I need to merge into one. One of the cells is a number > > > anywhere from 1-16 or more. What I need is for the number in this cells to > > > always be concatenated into a 2 digit number. Example: > > > Cell A1 = USA123-4555678952 > > > Cell A2 = CD > > > Cell A3 = 4 > > > I need the final output to be 234555678952CD04. > > > So far I have the following formula: > > > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&A3 but this doesn't put "04" after the > > > CD. It only puts "4" after it. Any ideas for Excel 2003? > > > Thanks.
From: Rick Rothstein on 4 Jun 2010 13:20 Does A3 contain the payment number? If so, I don't get 01 with Gary''s Student's formula like you posted; however, I do get the beginning part of the resulting number to be an incorrect value. For the values you posted in A1 and A2 and 10 in A3, I get his formula to display... 124555678952CD10 whereas I think the value you wanted for these values is this instead... 234555678952CD10 If that latter value is correct, then take a look at the formula I posted because that is the value it calculates to. -- Rick (MVP - Excel) "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message news:15B0073E-E980-4DD9-BADD-BE2B4A174BD4(a)microsoft.com... > Gary, > If the payment # is 10, this formula records it as 01. How do I write the > formula so pmt 1 comes out as 01 and pmt 10 comes out as 10 and pmt 15 > comes > out as 15, etc? > Thanks. > > > "Gary''s Student" wrote: > >> A tiny trick: >> >> =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00") >> -- >> Gary''s Student - gsnu201003 >> >> >> "Nadine" wrote: >> >> > I have 3 cells I need to merge into one. One of the cells is a number >> > anywhere from 1-16 or more. What I need is for the number in this >> > cells to >> > always be concatenated into a 2 digit number. Example: >> > Cell A1 = USA123-4555678952 >> > Cell A2 = CD >> > Cell A3 = 4 >> > I need the final output to be 234555678952CD04. >> > So far I have the following formula: >> > =RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&A3 but this doesn't put "04" >> > after the >> > CD. It only puts "4" after it. Any ideas for Excel 2003? >> > Thanks.
First
|
Prev
|
Pages: 1 2 Prev: Racking my brain on sumif Next: Calculating Bond Amortization using Excel functions |