Concatenation help
in [Functions]
|
From: Nadine on 3 Jun 2010 17:51 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: Gary''s Student on 3 Jun 2010 18:04 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: Nadine on 3 Jun 2010 18:08 THANK YOU!!!!! I knew you'd come through for me. "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: Gary''s Student on 3 Jun 2010 18:22 You are welcome. Thanks for the feedback. -- Gary''s Student - gsnu201003 "Nadine" wrote: > THANK YOU!!!!! I knew you'd come through for me. > > "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 3 Jun 2010 22:50 Two characters and one function call shorter... =RIGHT(SUBSTITUTE(A1,"-",""),12)&A2&TEXT(A3,"00") -- Rick (MVP - Excel) "Gary''s Student" <GarysStudent(a)discussions.microsoft.com> wrote in message news:0187C8EB-A6C8-4416-B405-674C022244B2(a)microsoft.com... > 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.
|
Next
|
Last
Pages: 1 2 Prev: Racking my brain on sumif Next: Calculating Bond Amortization using Excel functions |