Truncate a specific character in a column ,some cells in the column might have that paricular character but some dont.

[WilsonST]

Hi , I am seeking for help of truncate a specific character from range of words in a columns.
In the raange of column, some cell might contain that specific character but some dont.
The result will then substitutes its original data.
any one can help ?

I tried
Sub remove_text_after_char()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = Left(cell, InStr(cell, "@") - 1)
Next cell
End Sub

but this will give an error when a cell doesnt consists of that character "@" and yet its result will be copied to next column ( cell)
but I was thinking of getting the result to replace its original data.

 

[PeteWright]

Public Sub TruncateText()
   Dim rng, cell As Range
   Dim i As Integer, str As String
   Set rng = Selection
   For Each cell In rng
      For i = 1 To cell.Characters.Count
         If cell.Characters(i, 1).Text = "@" Then
            cell.Value2 = cell.Characters(1, i - 1).Text
         End If
      Next i
   Next cell
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is another option that could try with a copy of your workbook.

Sub remove_text_after_char_v2()
  Selection.TextToColumns , xlDelimited, , , False, False, False, False, True, "@", Array(Array(1, 1), Array(2, 9))
End Sub

 

[Fluff]

Another option

Sub WilsonST()
   With Selection
      .Value = Evaluate(Replace("left(#,find(""@"",#&""@"")-1)", "#", .Address))
   End With
End Sub

 

[WilsonST]

Thanks you guys for help ..i ma newbie try to simplify my daily job using own construct of multiple small program ( sub ) into a single one.
You Guys Are Really Awesome !

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.