Array question

davidtn2

New Member
Joined
May 2, 2014
Messages
8
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"
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Last edited:
Upvote 0
I know how you feel!

Perhaps VBA is something you'd consider?

Regards
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top