If cell contains text then ......

[1ceman]

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

 

[Dave Peterson]

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

Maybe????

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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[1ceman]

Ampersand !!!!!

Thanks guys !!!!

 

[1ceman]

Guys, obviously, it works well.

Thanks for that.

Next Q......

Can this be done in vba using xlformulas?

Cheers

Jef

 

[Dave Peterson]

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.