 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006How to concatenate an array of four stings using formulae (not UDF)
Hi All, I need to work out how to concatenate an array of four strings skin to the following (which does not work): =CONCATENATE({"AAA";"BBB";"CCC";"DDD"}) ="AAABBBCCCDDD" Looks deceptively simple, but I am stumped (or stupid!) Any ideas? Thanks, Alan.  SignatureThe views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb ="aaa" & "bbb" & "ccc" &"DDD" > Hi All, > [quoted text clipped - 12 lines] > > Alan. > ="aaa" & "bbb" & "ccc" &"DDD" Hi, Thanks for the reply. How do I get to that from the array though? I have this: {"AAA";"BBB";"CCC";"DDD"} I do not have those elements in four separate cells. Thanks, Alan. SignatureThe views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb =CONCATENATE("AAA","BBB","CCC","DDD") > Hi All, > [quoted text clipped - 12 lines] > > Alan. > =CONCATENATE("AAA","BBB","CCC","DDD") Hi JMB, Thanks for the reply. How do I get to that from the array though? I have this array: {"AAA";"BBB";"CCC";"DDD"} I do not have those elements in four separate cells. Thanks, Alan. SignatureThe views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb Let's say the array is called MyArray =INDEX(MyArray, 1)&INDEX(MyArray, 2)&INDEX(MyArray, 3)&INDEX(MyArray, 4) > > =CONCATENATE("AAA","BBB","CCC","DDD") > [quoted text clipped - 13 lines] > > Alan. Perfect! Thanx, Alan. SignatureThe views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb > I have this array: > {"AAA";"BBB";"CCC";"DDD"} How is this array generated? =INDEX(array,1)&INDEX(array,2)&INDEX(array,3)&INDEX(array,4) Biff >> =CONCATENATE("AAA","BBB","CCC","DDD") > [quoted text clipped - 13 lines] > > Alan. Perfect too! Thanx, Alan. SignatureThe views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb >Hi All, > [quoted text clipped - 12 lines] > >Alan. Well, you could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ This add-in is easily distributed with workbooks if that is an issue. and then use the formula: =MCONCAT({"AAA";"BBB";"CCC";"DDD"}) There may be more efficient ways of doing this using native functions, but it's late, so I can only offer the following: =INDEX({"AAA";"BBB";"CCC";"DDD"},1)& INDEX({"AAA";"BBB";"CCC";"DDD"},2)& INDEX({"AAA";"BBB";"CCC";"DDD"},3)& INDEX({"AAA";"BBB";"CCC";"DDD"},4) --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.