filtering out a string of numbers out of a cell with text and numbers

[brucejd]

I want to create a column of data that is comprised from filtering out
string of numbers from another column that contains cells with both tex
and numbers.

Example, the following is an example of the data contained within
cell:

Key Production - Cabs Farms #1-5 / Lay & test approx. 2100' of 4
.156"wt x-42 FBE ERW steel, set a 2" 600# Simplex meter station w/EFM
radio & relief valve, set a 4" 600# above ground valve setting withou
check valve and make a 4x4 dead line tie.

I want filter out the number 2100 automatically. This number could b
anywhere from 1-7 digets in length. It is always followed by th
single quote symbol ', and proceeded by a space. It also alway
follows the word "Lay"

 

[Jason Morin]

You could use:

=MID(SUBSTITUTE(lay," ","@$!%^",LEN(lay)-LEN(SUBSTITUTE
(lay," ",""))),FIND("@$!%^",SUBSTITUTE(lay," ","@$!%^",LEN
(lay)-LEN(SUBSTITUTE(lay," ",""))))+5,999)

where "lay" is a defined name (Ctrl+F3) for:

=MID(A2,SEARCH("lay",A2),SEARCH("'",A2)-SEARCH("lay",A2)
+1)

This assumes your first cell with the string is in A2.

HTH
Jason
Atlanta, GA

 

[Guest]

If your data is in column C, say, then in H2, say, enter:
=NOT(ISERROR(FIND(" 2100'",C2)))
then use data/filter/advanced filter on column C, select Criteria range of H1:H2.

Bob Umlas
Excel MVP