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????

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

[CLR]

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

Vaya con Dios,
Chuck, CABGx3


"1ceman" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[1ceman]

Ampersand !!!!!

Thanks guys !!!!

[1ceman]

Guys, obviously, it works well.

Thanks for that.

Next Q......

Can this be done in vba using xlformulas?

Cheers

Jeff

[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.


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