find @ and put { before start of email in A COLUMN need vb

[shrinivasmj]

all data is in excel in A column need a vb were it search for @ in each cell and put { diameter before the email id

vb or macro

in a cell data is

"August 5,2004" 06:16:00 PM tonytruex klblessed@hotmail com Chris Green rt 2 box 67 Fort Polk ga 94137 (310)639-4841 ("408,1263-3208 Anytime Refinance First With Debt and CashOut 270438 Multy Family Home 207490 2003 333694 7.104 Adjustable 9 _Excelent BAE SYSTEMS 7 8497 Visa Gold Credit card 5813638338123290

replace to

"August 5,2004" 06:16:00 PM tonytruex { klblessed@hotmail com Chris Green rt 2 box 67 Fort Polk ga 94137 (310)639-4841 ("408,1263-3208 Anytime Refinance First With Debt and CashOut 270438 Multy Family Home 207490 2003 333694 7.104 Adjustable 9 _Excelent BAE SYSTEMS 7 8497 Visa Gold Credit card 5813638338123290

u can it as to put { before e mail id { klblessed@hotmail com

 

[Michael M]

Couldn't you simply use Find / Replace to do this ??

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub AddOpeningBraceBeforeEmailAddress()
  Dim Cell As Range
  On Error GoTo NoData
  For Each Cell In Cells.SpecialCells(xlConstants)
    If InStr(Cell.Value, "@") Then
      Cell.Value = Trim(Application.Replace(Cell.Value, InStrRev(" " & Cell.Value, " ", InStr(Cell.Value, "@")), 0, "{ "))
    End If
  Next
NoData:
End Sub[/td]
[/tr]
[/table]

 

[shrinivasmj]

hi
thanks it working , one more i need after @ to put at end {

 

[Rick Rothstein]

hi
thanks it working , one more i need after @ to put at end {

Here it is...

[table="width: 500"]
[tr]
	[td]Sub AddClosingBraceAfterEmailAddress()
  Dim Cell As Range
  On Error GoTo NoData
  For Each Cell In Cells.SpecialCells(xlConstants)
    If InStr(Cell.Value, "@") Then
      Cell.Value = Trim(Application.Replace(Cell.Value, InStr(InStr(Cell.Value, "@"), Cell.Value & " ", " "), 0, " }"))
    End If
  Next
NoData:
End Sub[/td]
[/tr]
[/table]

Did you want a single macro to do both at the same time? If so...

[table="width: 500"]
[tr]
	[td]Sub AddOpeningAndClosingBraceAroundEmailAddresses()
  Dim Cell As Range
  On Error GoTo NoData
  For Each Cell In Cells.SpecialCells(xlConstants)
    If InStr(Cell.Value, "@") Then
      Cell.Value = Trim(Application.Replace(Cell.Value, InStrRev(" " & Cell.Value, " ", InStr(Cell.Value, "@")), 0, "{ "))
      Cell.Value = Trim(Application.Replace(Cell.Value, InStr(InStr(Cell.Value, "@"), Cell.Value & " ", " "), 0, " }"))
    End If
  Next
NoData:
End Sub[/td]
[/tr]
[/table]