Merging two working formulas

Bill Golden
Bill Golden used Ask the Experts™
on
I have the following working formula

=IF($Q$11<>""," "&$Q$11&" ","You must enter a the Company's City and State in Q11!")

Which yields City, State & Zip Code, i.e. New York City, New York 12345

And this formula

=LEFT($Q$11,FIND("",$Q$11,FIND(", ",$Q$11)+3))

Which yields City and State with no Zip Code, i.e. New York City, New York

I need to combine the second formula with the first formula.  A snippet with all pertinent cells is attached.
EE-Snippet-CalcXLM-Report-Zip-Code-.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try

=IF($Q$11<>""," "&LEFT($Q$11,FIND("",$Q$11,FIND(", ",$Q$11)+3))&" ","You must enter a the Company's City and State in Q11!")

Open in new window

Regards
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

not sure I completely understand what you want to achieve, but if you want to plug the result of the second formula into the IF statement of the first formula, then maybe

=IF($Q$11<>""," "&LEFT($Q$11,FIND("",$Q$11,FIND(", ",$Q$11)+3))&" ","You must enter a the Company's City and State in Q11!")

cheers, teylyn
Bill GoldenExecutive Managing Member

Author

Commented:
Thanks, both solutions work perfectly because they were identical.  I do stop and examine each correct answer to learn, albeit somewhat slowly, how to do this myself.

Working on this project helps get through the chemo and radiation treatments. Some of you will never know how much EE has meant to me during this difficult time.

Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial