Array question

[davidtn2]

Is there a way to make the cell show the array?

For example, in cell B1, i have a formula and when i click F9 it shows {"D";"a";"v"}.

Can I somehow make cell B1 show "av"??

Thanks in advance.

Reason: I'm trying to alternate between upper and lower case of a text. In Cell A1, i have "David"

My formula in B1 is =IF(ISODD(ROW(INDIRECT("1:"&LEN(A1)))),UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1)),1)))

Im trying to make B1 show "DaViD"

 

[XOR LX]

Hi.

Unfortunately not.

What's more, since none of Excel's worksheet functions designed for concatenation are capable of operating on arrays (at least not in the sense you would like), I'm afraid you're left with the choice of either writing unfeasibly long formulas, e.g.:

=UPPER(MID(A1,1,1))&LOWER(MID(A1,2,1))&...

etc.

or of using VBA.

It's perhaps one of the most frustrating things about working in Excel, I would say. Why Microsoft haven't got round to giving array-processing functionality to the "text" functions (it's not just concatenation which suffers this drawback) I really don't know.

Regards

 

[davidtn2]

Thanks for the reply. Been wasting hours trying to figure it out. Guess I can stop now.

 

[XOR LX]

I know how you feel!

Perhaps VBA is something you'd consider?

Regards