How to Merge an array into Single cell

earthworm

Active Member
Joined
May 19, 2009
Messages
349
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This might look simple but it is not . How can you merge the multiple values into single cell

={"D";"C";"B";"A"}

With reference to above , the first answer is "D" i want it to appear as DCBA . The length of result can vary .

The reason of my above query is I want to reverse a value of single cell and them merge my result into single cell

example

ABCD = DCBA

Formula : =MID(A1,LARGE(ROW(INDIRECT(1&":"&LEN(A1))),ROW(INDIRECT(1&":"&LEN(A1)))),1)

With reference to above formula , the formula is showing me the result , however its not showing the combine result in single value . I can get the answer my merging the formula according to length of text / value but that is manual activity . What if the length of value varies .

I don't want to use excel ad in.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Excel is hopeless at concatenation so there is no simple way using built-in functions. For up to 20 characters you can use:

=MID(A1,20,1)&MID(A1,19,1)&MID(A1,18,1)&MID(A1,17,1)&MID(A1,16,1)&MID(A1,15,1)&MID(A1,14,1)&MID(A1,13,1)&MID(A1,12,1)&MID(A1,11,1)&MID(A1,10,1)&MID(A1,9,1)&MID(A1,8,1)&MID(A1,7,1)&MID(A1,6,1)&MID(A1,5,1)&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1)

Or you can use this UDF:

Code:
Public Function RevStr(Rng As Range)
    RevStr = StrReverse(Rng.Text)
End Function

like this:

=RevStr(A1)
 

earthworm

Active Member
Joined
May 19, 2009
Messages
349
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thank you soo much for this UDF

By the way whats UDF and how can i explore more function like this
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
UDF is an acronym for User Defined Function. Search Google for 'UDF Excel' and you will get lots of hits.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,323
Members
409,695
Latest member
EmmaFos

This Week's Hot Topics

Top