How to concatenate an array of four stings using formulae (not UDF)

[Alan]

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.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

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

 

[PY & Associates]

="aaa" & "bbb" & "ccc" &"DDD"

 

[Guest]

=CONCATENATE("AAA","BBB","CCC","DDD")

 

[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.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

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

 

[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.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

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

 

[Guest]

Let's say the array is called MyArray

=INDEX(MyArray, 1)&INDEX(MyArray, 2)&INDEX(MyArray, 3)&INDEX(MyArray, 4)

 

[Biff]

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

 

[Ron Rosenfeld]

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.

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

 

[Alan]

Perfect!

Thanx,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

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

 

[Alan]

Perfect too!

Thanx,

Alan.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

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