You could always write a UDF that returns the cell contents as an array
Function SplitCell(cell As Range, Delim As String)
SplitCell = Split(cell, Delim)
End Function
which assumes the cell contains say 1,2,3,4 and you get it with
SplitCell(A1,",")
This is not much in itself, but it can be incorporated in others, like say
=MATCH("2",SplitCell(A1,","),0)
Note that the UDF I gave returns an array of strings, so if they are numbers
that you want to check, you have to pass as strings.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Because the array will have different lengths in different rows. In
> one row, The array might be {1;2;5;6}, in another, {3;8}, and in a
> third, {1;2;3;4;5;6;7;8;9;10;11;12}. The different lengths would
> require different widths; some would require only two columns, some
> 12. Putting them into one column would greatly increase the
> readability, and reduce the size, of the sheet.
>
> Martin
>
> On Sep 19, 7:40 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> If you want it general, why not just put the values in separate cells and
>> have a real array?
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> <e271828182845904...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > Thanks for both your answers. However, I would like the formula using
>> > the array to be general, and not tied up to a specific (static)
>> > variable. Are you saying that this cannot be done?
>>
>> > Martin
>>
>> > On Sep 19, 10:33 am, "Sebation" <ec.wangq...@gmail.com> wrote:
>> >> once you store the {2;4;5;8} in a cell ,it will not be an array,while
>> >> your
>> >> match() function in the array.
>> >> but u can use --"Define name" --to archive this.
>> >> i.e.define an array name "AN"
>> >> ={2;4;5;8}
>> >> while u can in the cell enter
>> >> =match(5,AN,0)
>>
>> >> HTH
>>
>> >> Regards,
>> >> Sebation.G
>>
>> >> <e271828182845904...@gmail.com>
>> >> ??????:1190184710.468896.169...@n39g2000hsh.googlegroups.com...
>>
>> >> >I want to use the MATCH function on an array stored in a cell.
>> >> >MATCH(5;
>> >> > {2;4;5;8};0) returns 3, as it should. However, if I set e.g. C3 to
>> >> > {2;4;5;8}, MATCH(5;C3;0) gives #N/A. It seems Excel is interpreting
>> >> > C3
>> >> > as a 1x1 array of cells, rather than reading the value of C3; i.e
>> >> > the
>> >> > array {2;4;5;8}. The question is how to circumvent this? I have not
>> >> > been able to find a function that forces interpretation of the cell
>> >> > reference.
>>
>> >> > Martin- Hide quoted text -
>>
>> >> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>
Similar Threads
