From: Cesar Urquidi on 18 May 2010 13:48 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 From: Luke M on 18 May 2010 14:09 Change the source formula in column C to: =IF(COUNTA(A2:B2)<>2,"",A2&" Qty. "&B2) -- Best Regards, Luke M "Cesar Urquidi" wrote in message news:8686F113-A340-4635-9C4B-36C590F0E551(a)microsoft.com...> 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 From: Cesar Urquidi on 18 May 2010 15:02 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 "Luke M" wrote: > Change the source formula in column C to: > =IF(COUNTA(A2:B2)<>2,"",A2&" Qty. "&B2) > > -- > Best Regards, > > Luke M > "Cesar Urquidi" wrote in message > news:8686F113-A340-4635-9C4B-36C590F0E551(a)microsoft.com... > > 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 > > > . >  |  Pages: 1 Prev: How to Import data from a different .xls fileNext: Merging reports