Revise concatenated formula in Excel so only executed under certain conditions

In the attached spreadsheet, I would like to revise the formula in Column C as follows:

Current formula:
The current formula in Column C concatenates Columns A & B

Revised formula:
Would like the formula to concatenate Columns A & B only when the format of Column A is ##- and the format of Column B is ####, otherwise Column C is to remain blank.

Thanks!
Andrea
EE-Example-July2015.xlsm
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfarrarCommented:
Try this:

=IF(AND(LEN(A2)=3,LEN(B2)=4), CONCATENATE(A2,B2),"")
0
Russ SuterCommented:
Try changing your formula as follows:

=IF(OR(ISBLANK(A2), ISBLANK(B2)), "", CONCATENATE(A2,B2))
0
Saurabh Singh TeotiaCommented:
Small changes in tomfarrar to make it more robust which will be this..

=IF(AND(LEN(A2)=3,RIGHT(A2,1)="-",LEN(B2)=4),A2&B2,"")

Open in new window


Saurabh...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If your requirement is to check whether col. A and B contains numbers and have a definite length, the most accurate formula would be this....

=IF(AND(ISNUMBER(LEFT(A2,2)+0),RIGHT(A2,1)="-",LEN(B2)=4,ISNUMBER(B2+0)),A2&B2,"")

Open in new window


The above formula will make sure that the col. A and B contains numbers and will not concatenate if A2 is like 1a- and B2 is like abcd.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Thanks, everyone, for your quick responses!

I tested all the formulas provided, and all worked with the exception of the following one, which seemed to result in Column C being populated regardless of the format in Columns A & B:

=IF(OR(ISBLANK(A2), ISBLANK(B2)), "", CONCATENATE(A2,B2))

I appreciated the accuracy of sktneer's formula, so identified that one as the best solution.

Cheers,
Andrea
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andrea! Glad I could help. :)
0
tomfarrarCommented:
Nice working with you, Andrea.  Thanks for the assist, Saurabh.
0
Saurabh Singh TeotiaCommented:
Andrea.. HTH..Glad it worked for you..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.