| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
| IF Statement nightmare | Excel Discussion (Misc queries) | |||
| How to calculate a sum as one outcome of an IF statement | Excel Worksheet Functions | |||
| How do I fix a circular reference in a financial statement? | Excel Discussion (Misc queries) | |||
| Statement | Excel Worksheet Functions | |||
Hybrid Mode
