Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks

I'm not sure where B3 comes into it because you describe haveing Sales in B2
and names in C2, C3 and C4 but if I have understood put this in any cell and
drag down.

=$B$2&" "&C2

And see if that's what you require.

Mike

"Sunnyskies" wrote:

Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks

=$B$2&C2

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sunnyskies" wrote in message
...
Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks

I would use the feature in ASAP Utilities to fill down the blanks in column B
with the values above and then do a straight concatenation, like
=B2&" "&C2, copied down

ASAP Utilities is a free add-in available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3




"Sunnyskies" wrote:

Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks

Afternoon Mike,

Cell B2 cannot be a fixed reference.

Here is the columns:

Bethlehem SHELL
TOTAL
Bloemfontein SHELL
TOTAL
Hartswater TOTAL
Kimberley SHELL
TOTAL
Kroonstad CHEVRON
SHELL
TOTAL
Maseru TOTAL
Sannieshof TOTAL

Do you see that at Kroonstad there are three Oil Companies listed, I need to
concatenate the Town with the Oil company but at Kroonstad the formula does
not work so well.

Thanks



"Mike H" wrote:

I'm not sure where B3 comes into it because you describe haveing Sales in B2
and names in C2, C3 and C4 but if I have understood put this in any cell and
drag down.

=$B$2&" "&C2

And see if that's what you require.

Mike

"Sunnyskies" wrote:

Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks

The table is a pivot table, in which I would like to concatenate outside the
table grid.

"CLR" wrote:

I would use the feature in ASAP Utilities to fill down the blanks in column B
with the values above and then do a straight concatenation, like
=B2&" "&C2, copied down

ASAP Utilities is a free add-in available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3




"Sunnyskies" wrote:

Afternoon from Sunny SA,

Picture this.

In column B I have a job title in row 2 (Sales).
In column C I have applicant names in row 2, 3 & 4 (Peter, John & Paul)

Now I want to concatenate column B & C to get Sales Peter, Sales John and
Sales Paul.

How do I get Sales against Paul with a formula.

I was using the formual If(B4="",B3&C4, B4&C4), but this does not work if
there is a blank in cell B3.

Thanks