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"
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,168
Members
414,215
Latest member
SRGDZMCS

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