|
IF Statement??
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 |
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) "Cathy Landry" wrote in message ... 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 |
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 "Bob Phillips" wrote: 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) "Cathy Landry" wrote in message ... 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 |
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" wrote: 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) "Cathy Landry" wrote in message ... 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 |
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) "Cathy Landry" wrote in message ... 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 "Bob Phillips" wrote: 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) "Cathy Landry" wrote in message ... 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 |
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(ISNUMB ER(MATCH(".",MID(A14,ROW($1:$19),1),0)),1,0)) This formula assumes that the reference cell is A14 Regards, Ashish Mathur "Cathy Landry" wrote: 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 |
Doesn't work for me.
-- HTH RP (remove nothere from the email address if mailing direct) "Ashish Mathur" wrote in message ... 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,R OW($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 "Cathy Landry" wrote: 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 |
| All times are GMT +1. The time now is 06:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com