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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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:

davidtn2

New Member
Joined
May 2, 2014
Messages
8
Thanks for the reply. Been wasting hours trying to figure it out. Guess I can stop now.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
I know how you feel!

Perhaps VBA is something you'd consider?

Regards
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,036
Messages
5,856,959
Members
431,841
Latest member
jaybeem

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
Top