How to Merge an array into Single cell

[earthworm]

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.

 

[Andrew Poulsom]

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:

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

like this:

=RevStr(A1)

 

[earthworm]

Thank you soo much for this UDF

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

 

[Andrew Poulsom]

UDF is an acronym for User Defined Function. Search Google for 'UDF Excel' and you will get lots of hits.