Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MAY BE IMPOSSIBLE (2003/10.2614.2625)

    Can anyone think of a way to find whether a particular character occurs within an array of cells and if so in which of them? If, for example, some are empty, some contain several alphanumeric characters, and some just one, is there a test for whether 1 or A occurs anywhere and if so in which cell?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MAY BE IMPOSSIBLE (2003/10.2614.2625)

    Lets say we're looking in cells A1 to A4 and the character to look for is in C2:

    This array formula should give the content of the first cell that contains the character to look for:

    =INDEX(A1:A4,MIN(IF(ISERROR(FIND(C2,A1:A4)),"",ROW (A1:A4))))

    USe control-shift-enter to enter this formula!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MAY BE IMPOSSIBLE (2003/10.2614.2625)

    Fantastic! I don't understand it but it works. And from that of course it is straightforward to derive the position in the array of the cell. Thank you, Jan, I am very grateful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •