Joining Array

Hi Kyle,

Try:

=SUMPRODUCT(10^(LEN($J2)-ROW(INDIRECT("$1:$"&LEN($J2))))*LOOKUP((1*(CODE(MID(UPPER($J2),ROW(INDIRECT("$1:$"&LEN($J2))),1)))),{65,68,71,74,77,80,84,87},ROW($2:$9)))

Regards

Strange. My last post doesn't seem to have registered.

Unfortunately not. This type of 'workaround' will only work on numerical concatenation (and only in specific cases).

And unless Microsoft have got round to it in 2013 (which I doubt), us worksheet function-users will continue to be left mystified as to why a concatenating function which can be passed arrays is still not in existence.

Guess that leaves multiple string-concatenation firmly in the VBA field for the time being.

Regards

I use frequently this excellent UDF to concatenate arrays/ranges

Please Login or Register  to view this content.

Example

	A	B	C	D	E
1	Employee	Dept		Dept	Employees
2	Anthony	Dept1		Dept1	Anthony, Barbara, Bob
3	Barbara	Dept1		Dept2	Charles, Diana, Mike
4	Bob	Dept1		Dept3	Mark, Richard, Robert, William
5	Charles	Dept2
6	Diana	Dept2
7	Mike	Dept2
8	Mark	Dept3
9	Richard	Dept3
10	Robert	Dept3
11	William	Dept3

Array formula in E2 copied down
=SUBSTITUTE(Aconcat(IF($B$2:$B$11=D2,", "&$A$2:$A$11,"")),", ","",1)
Ctrl+Shift+Enter

Once i have come to know.. that a Excel Expert goes to COMA, when he trying to reverse a string using formula..

hope, this time, i will live experience the same.. convert a string to Phonetic Number..

Originally Posted by Debraj Roy

Once i have come to know.. that a Excel Expert goes to COMA, when he trying to reverse a string using formula..

hope, this time, i will live experience the same.. convert a string to Phonetic Number..

Hi

Reverse a string is not so tough, but what you mean by convert a string to Phonetic Numbers? (sorry for my ignorance...)

Marcelo,

Check out this UDF:

http://www.excelforum.com/tips-and-t...geravatar.html

The resultant formulas are much simpler than the equivalent formulas needed by using the aconcat/mconcat UDFs.

Originally Posted by Tony Valko

Marcelo,

Check out this UDF:

http://www.excelforum.com/tips-and-t...geravatar.html

The resultant formulas are much simpler than the equivalent formulas needed by using the aconcat/mconcat UDFs.

Tony,

Thank you for the link.

I think you are talking about post 13 (jindon's functions), right?
They really make the result formula much simpler.

Next step: understand how they work

M.

I was referring to the UDFs by tigeravatar.

The code is more extensive and accounts for the most common conditions when wanting to conditionally concatenate ranges. Most of the processing is done within the UDF which allows for simple formulas.

Hi marcelo..

Guru want to convert a name to this..

phone-keypad.jpg

I mean ICICI's USA's customer care Number is ..

1-866-ICICI-4U
which is 1-866-424-2448

USING FORMULA..

PS: BTW, I will love to learn "Reverse a string" using formula..

Reverse a string using a formula (uses UDF Aconcat above in #9)

	A	B
1	abcde ghi klm	mlk ihg edcba
2	123456789 adhb	bhda 987654321
3	1a 2b 3c 4d 5e	e5 d4 c3 b2 a1

Array formula in B1 copied down
=Aconcat(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

Maybe something like this

Make a table in, say, D1:E36 like below - note that the numbers in blue in column D are entered as text, i.e., '0 '1 '2....

	D	E
1	0	0
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	A	2
12	B	2
13	C	2
14	D	3
15	E	3
16	F	3
17	G	4
18	H	4
19	I	4
20	J	5
21	K	5
22	L	5
23	M	6
24	N	6
25	O	6
26	P	7
27	Q	7
28	R	7
29	S	7
30	T	8
31	U	8
32	V	8
33	W	9
34	X	9
35	Y	8
36	Z	9

	A	B
1	1-866-ICICI-4U	1-866-424-2448
2	1-800-PRINT-ME	1-800-774-6863
3	1-800-FLOWERS	1-800-356-9377
4	1-800-APL-CARE	1-800-275-2273

Array formula in B1 copied down
=TEXT(Aconcat(LOOKUP(MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"-","")))),1),$D$1:$D$36,$E$1:$E$36)),"0-000-000-0000")
confirmed with Ctrl+Shift+Enter

Originally Posted by mlcb

Reverse a string using a formula (uses UDF Aconcat above in #9)

	A	B
1	abcde ghi klm	mlk ihg edcba
2	123456789 adhb	bhda 987654321
3	1a 2b 3c 4d 5e	e5 d4 c3 b2 a1

Array formula in B1 copied down
=Aconcat(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

Sorry, this is silly. VBA has a function that reverts a string.

Please Login or Register  to view this content.

Using just formulas i think it's really tough (i dont want to go into a COMA )

M.

Originally Posted by Tony Valko

I was referring to the UDFs by tigeravatar.

The code is more extensive and accounts for the most common conditions when wanting to conditionally concatenate ranges. Most of the processing is done within the UDF which allows for simple formulas.

Tony,

I'll take a look at tigeravatar's function.

M.