Extracting specific info from cells

[johnhaskell]

Hello everyone.

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

If and only if an address contains the string "zone" then I want to pu
that zone number in another column.
For instance, an address might contain "zone 18". I would want the ne
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'
probably making it way too complicated.

Thanks in advance for any help you all could give

 

[GS]

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

 

[Ron Rosenfeld]

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.

 

[GS]

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

 

[johnhaskell]

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

;1602764']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.discussio

 

[Ron Rosenfeld]

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*)

 

[Ron Rosenfeld]

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)))

 

[Ron Rosenfeld]

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.