Need help with a formula, please!!!

[Cesar Urquidi]

Hello,
I have 2 sheets; "Sheet1" & "Sheet2":

Sheet1:

Column A Column B Column C
Part Number Qty. P/N & Qty.
10100 1 10100 Qty. 1
10200 1 10200 Qty. 1
10300 2 10300 Qty. 2
Qty.

Note: Column "C" is the result of a formula joining columns "A" & "B". This
is the formula:

=CONCATENATE(A2," ","Qty. ",B2)

The text " Qty. " (SpaceQty.Space) in column "C" (5th row) is the result of
the formula, because there is nothing in "Part Number" & "Qty." cells (5th
row).

Sheet2:

Column A
P/N & Qty.
Formula???

I need a formula to replace " Qty. " (SpaceQty.Space) with blank.
Only when the cell contains " Qty. " (SpaceQty.Space). If the cell is "10100
Qty. 1",
do not replace.

I could use the find and replace, but I can't, because I will protect
"Sheet1", and find & replace does not work on protected sheets.

Help please!!!

Thank you,
Cesar Urquidi

 

[Luke M]

Change the source formula in column C to:
=IF(COUNTA(A2:B2)<>2,"",A2&" Qty. "&B2)

 

[Cesar Urquidi]

Hello Luke,
It worked fine, but if there is a "Part Number" in "A5" with no "Qty." in
"B5", then nothing appears in "C5" using your formula.
Even if the part number has no qty., I still want to display it.

Any other idea?!!!

Thank you,
Cesar