Hi,

Having a braindead morning here.

I have 3 columns a=number, b=customer, c=quantity.

Column B has various customer names.
In column D, I need a formula which says if b2 contains text of any
kind then show the text with (2) tagged on the end.
ie if collins is showing in b2 then d2 should show Collins(2)

Apologies for this, head all over


--
1ceman
------------------------------------------------------------------------
1ceman's Profile: http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

=if(b2="","",b2&"(2)")

Maybe????

1ceman wrote:

Hi,

Having a braindead morning here.

I have 3 columns a=number, b=customer, c=quantity.

Column B has various customer names.
In column D, I need a formula which says if b2 contains text of any
kind then show the text with (2) tagged on the end.
ie if collins is showing in b2 then d2 should show Collins(2)

Apologies for this, head all over

--
1ceman
------------------------------------------------------------------------
1ceman's Profile: http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

--

Dave Peterson

=IF(ISTEXT(B2),B2&2,"")

Vaya con Dios,
Chuck, CABGx3


"1ceman" wrote in
message ...

Hi,

Having a braindead morning here.

I have 3 columns a=number, b=customer, c=quantity.

Column B has various customer names.
In column D, I need a formula which says if b2 contains text of any
kind then show the text with (2) tagged on the end.
ie if collins is showing in b2 then d2 should show Collins(2)

Apologies for this, head all over


--
1ceman
------------------------------------------------------------------------
1ceman's Profile:
http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

Ampersand !!!!!

Thanks guys !!!!


--
1ceman
------------------------------------------------------------------------
1ceman's Profile: http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

Guys, obviously, it works well.

Thanks for that.

Next Q......

Can this be done in vba using xlformulas?

Cheers

Jeff


--
1ceman
------------------------------------------------------------------------
1ceman's Profile: http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

Are you trying to update the values in place?

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
myCell.Value = myCell.Value & "(2)"
End If
Next myCell
End Sub

or if you wanted to update column C:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'clear out the cell????
mycell.offset(0,1).value = ""
Else
myCell.offset(0, 1).Value = myCell.Value & "(2)"
End If
Next myCell
End Sub

Be aware that the first routine will destroy any formulas in column B.


1ceman wrote:

Guys, obviously, it works well.

Thanks for that.

Next Q......

Can this be done in vba using xlformulas?

Cheers

Jeff

--
1ceman
------------------------------------------------------------------------
1ceman's Profile: http://www.excelforum.com/member.php...o&userid=34213
View this thread: http://www.excelforum.com/showthread...hreadid=540089

--

Dave Peterson