IF Statement??

[Guest]

Hello,

I have a drop down list of names/addresses. Within this list is a center
number preceded by a "#". I'd to be able to pull the actual center# i.e.
3325 to populate into my cost center cell.

25TH STREET Center# 3325 (old 0325)
920 East 25th St
Baltimore MD 21218

Is this possible?

Thank you!
Cathy

 

[Bob Phillips]

Hi Cathy,

Is this okay?

=MID(A20,FIND("# ",A20)+2,FIND(" ",A20,FIND("# ",A20)+2)-FIND("# ",A20)-2)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,

Yes, that worked great! I was also asked if I could only populate the
center# when another cell was not null.

Address cell is: J6
Descr cell is: F18 - cell K18 should only show a value when this cell is
filled in
Center# is: K18

Could I use the ISBLANK function with the MID function to do this?

Thank you very much for your help!
Cathy

 

[Guest]

Hi Bob,

I figured out the answer to my last question to you. This is what I used.

=IF(ISBLANK(F18),"",MID($J$6,FIND("# ",$J$6)+2,FIND(" ",$J$6,FIND("#
",$J$6)+2)-FIND("# ",$J$6)-2))

Thank you again.........it works beautifully!

 

[Bob Phillips]

Yes,

=IF(ISBLANK(F18),"",MID(A20,FIND("# ",A20)+2,FIND(" ",A20,FIND("#
",A20)+2)-FIND("# ",A20)-2))

or maybe

=(IF(F18="","",MID(A20,FIND("# ",A20)+2,FIND(" ",A20,FIND("#
",A20)+2)-FIND("# ",A20)-2))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi,

You may try an array formula (Ctrl+Shift+Enter)

1*MID(A14,MATCH(TRUE,ISNUMBER(1*MID(A14,ROW($1:$19),1)),0),COUNT(1*MID(A14,ROW($1:$19),1))+IF(ISNUMBER(MATCH(".",MID(A14,ROW($1:$19),1),0)),1,0))

This formula assumes that the reference cell is A14

Regards,

Ashish Mathur

 

[Bob Phillips]

Doesn't work for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)