View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Gord is offline
external usenet poster
 
Posts: 86
Default Use array/braces **WITHIN** a formula?

You have a couple of solutions to the abbreviating your formula.

I would just like to clarify your thoughts about curly braces inside
formulas.

Yes, you can use them to create arrays within a formula.

=LOOKUP(B1,{1,2,3,4},{"A","B","C","D"}))

which saves having to create a lookup table range on sheet.

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3 ,4,5,6,7,8,9})

Sums only numbers within a string like 1234abcd4321


Gord Dibben Microsoft Excel MVP


On Thu, 1 Sep 2011 05:48:16 -0700 (PDT), wal
wrote:

I typed the braces myself, which is incorrect. You have to enter
array formulas (including the braces) with control-shift-enter, but
that only works if the whole formula is an array formula, right?