If cell contains text then ......

  • Thread starter Thread starter 1ceman
  • Start date Start date
1

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
 
=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
Click to expand...
 
Guys, obviously, it works well.

Thanks for that.

Next Q......

Can this be done in vba using xlformulas?

Cheers

Jef
 
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.
 
Back
Top