Extracting specific info from cells

Hello everyone.

I'm looking at a column in Excel full of various addresses. Here's what I'm trying to do:

If and only if an address contains the string "zone" then I want to put that zone number in another column.
For instance, an address might contain "zone 18". I would want the new column to display 18.
This number would always be preceded by the word "zone".

I've tried to do this already using nested IFs, FINDs, SEARCHs, but I'm probably making it way too complicated.

Thanks in advance for any help you all could give.

johnhaskell formulated on Thursday :
> Hello everyone.
>
> I'm looking at a column in Excel full of various addresses. Here's what
> I'm trying to do:
>
> If and only if an address contains the string "zone" then I want to put
> that zone number in another column.
> For instance, an address might contain "zone 18". I would want the new
> column to display 18.
> This number would always be preceded by the word "zone".
>
> I've tried to do this already using nested IFs, FINDs, SEARCHs, but I'm
> probably making it way too complicated.
>
> Thanks in advance for any help you all could give.

Hint: according to your example, the number is preceeded by "zone "
which is 5 characters long. The number starts at the 6th character
AFTER the position returned by FIND()! The position returned by FIND()
is where "z" is located.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thu, 14 Jun 2012 22:23:51 +0000, johnhaskell > wrote:

>
>Hello everyone.
>
>I'm looking at a column in Excel full of various addresses. Here's what
>I'm trying to do:
>
>If and only if an address contains the string "zone" then I want to put
>that zone number in another column.
>For instance, an address might contain "zone 18". I would want the new
>column to display 18.
>This number would always be preceded by the word "zone".
>
>I've tried to do this already using nested IFs, FINDs, SEARCHs, but I'm
>probably making it way too complicated.
>
>Thanks in advance for any help you all could give.

Assuming you have Excel 2007 or later, and that the zone number will not be longer than ten (10) digits:

This formula must be **array-entered**:

=IF(ISERR(SEARCH("zone ",A1)),"",
MAX(IFERROR(--MID(A1,
SEARCH("zone ",A1)+5,ROW(INDIRECT("1:10"))),0)))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Ron,
I didn't offer a solution because John didn't say what, if any, text
follows the number. IOW, we'd need to see a pattern sample of the
entire string!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok for example, a string might say 6 CALLE A 10-47 COLONIA LODELPUENTES ZONA 11 DE MIXCO
(its actually zona, not zone) haha

Originally Posted by GS[_2_]

Ron,
I didn't offer a solution because John didn't say what, if any, text
follows the number. IOW, we'd need to see a pattern sample of the
entire string!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Fri, 15 Jun 2012 08:50:13 -0400, GS > wrote:

>Ron,
>I didn't offer a solution because John didn't say what, if any, text
>follows the number. IOW, we'd need to see a pattern sample of the
>entire string!

I believe my solution is independent of what ever might follow the number. However, it does depend on the zone BEING a number (he did describe it as a zone *number*)

On Fri, 15 Jun 2012 15:56:54 +0000, johnhaskell > wrote:

>Ok for example, a string might say 6 CALLE A 10-47 COLONIA LODELPUENTES
>ZONA 11 DE MIXCO
>(its actually zona, not zone) haha

Did you try my formula, making the obvious change from zone to zona?

=IF(ISERR(SEARCH("zone ",A1)),"",
MAX(IFERROR(--MID(A1,
SEARCH("zona ",A1)+5,ROW(INDIRECT("1:10"))),0)))

On Fri, 15 Jun 2012 15:56:54 +0000, johnhaskell > wrote:

>
>Ok for example, a string might say 6 CALLE A 10-47 COLONIA LODELPUENTES
>ZONA 11 DE MIXCO
>(its actually zona, not zone) haha
>
>

My previous alteration was incorrect as zone is used twice:

=IF(ISERR(SEARCH("zona ",A1)),"",
MAX(IFERROR(--MID(A1,
SEARCH("zona ",A1)+5,ROW(INDIRECT("1:10"))),0)))

and be sure to enter this as an **array formula** as I described in my original response.