On Fri, 30 Dec 2005 20:20:40 +0900, "kounoike" wrote:

"Ron Rosenfeld" wrote in message
.. .

I don't see what you mean when you say "plural match" in the examples given.
In the examples given, I see no need for an array form.

Perhaps I am missing something?
--ron

Thank you for your reply, Ron.

I'm Japaneses and my English is not as good enough to express what i want
to say as do in my mother tongue. but i'll try.
as you said, there is no need of array form in the examples given.
it's just my interest. if A1 has a data something like this

Kempapura, Hasigala Post, Hoskote Taluk, Bangalore Rural, #42/10,
Behind Dharmaraya Temple, Malinapet, Hona Hoskote Taluk

and want to extract every matches, How could i apply REGEX.MID for this?
i mean what i want in this case is "Hoskote Taluk" and "Hona Hoskote Taluk"

i can get these respectively by
=REGEX.MID(A1,"(?<=\s|^)[\s\w]*Taluk",1) and
=REGEX.MID(A1,"(?<=\s|^)[\s\w]*Taluk",2)
and i thought i could get these in the range by using as array formula,
as it is said to be able to use as array funntion.

Possibly, i misunderstood this function.

Thanks
keizi

OK, I think I understand what you are doing.

The function can return either a horizontal or vertical array. But to do so,
you need to specify the multiple arguments in the form of an array constant
within the function.

This behavior is similar to standard Excel functions.

For example, to return a vertical array of the 1st and 2nd instances, you could
enter:

=REGEX.MID(A1,"(?<=\s|^)[\w\s]*Taluk",{1;2})

as an array formula in adjacent rows. In other words, you could select B1 &
B2; enter the formula in B1 and then hold down <ctrl<shift while hitting
<enter. Excel should copy the formula into B2, and surround both with braces
{...}.

If you have some unknown number of matches, you could replace the array
constant in the above {1;2} with the function: ROW(INDIRECT("1:n")) where n is
the maximum number of matches that might be, and then enter the formula in
multiple cells as I described above.

Furthermore, if you require a horizontal array, change the array constant to
{1,2}.

Please note that I am using the default US separators in my equations. If your
Excel version has different separators, you may also need different separators
than I used in the array constant.


Also, you could obtain these results without using an array formula. For
example, in B1 you could enter the formula:

=REGEX.MID($A$1,"(?<=\s|^)[\w\s]*Taluk",ROW())

and copy/drag down. The ROW() would give you the sequential numbers for the
different matches.

To return in a horizontal fashion, you could, in B1, use the formula:

=REGEX.MID($A$1,"(?<=\s|^)[\w\s]*Taluk",COLUMN()-1)

and copy/drag across.


--ron