Deleting all characters to left of @ in a column

[Epe]

Hi everyone,

Is there a way to delete all characters to the left of @ in a column? The number of characters preceding @ will vary.

Thanks!

 

[hiker95]

Epe,

Welcome to the MrExcel forum.


I assume that your raw data is in column A, beginning in cell A1.


Sample raw data before the macro:

Excel Workbook
	A
1	Is there a way to delete all characters to the left of @ in a column?
2	The number of characters preceding @ will vary.
3
Sheet1

After the macro:

Excel Workbook
	A
1	@ in a column?
2	@ will vary.
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteLeftOfAt()
' hiker95, 06/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=559239
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If InStr(c, "@") > 0 Then c = Right(c, Len(c) - Application.Find("@", c, 1) + 1)
Next c
End Sub

Then run the DeleteLeftOfAt macro.

 

[T. Valko]

Hi everyone,

Is there a way to delete all characters to the left of @ in a column? The number of characters preceding @ will vary.

Thanks!

What version of Excel are you using?

 

[Epe]

Thank you so so much!

You just saved me from manually having to delete all the characters in front of the @! It would have taken forever.

Sorry T. Valko, I used hiker95's macro because that was the one I saw first.

Brilliant! Thank you both so much for taking the time to solve my problem.

Have a great day!

 

[T. Valko]

Thank you so so much!

You just saved me from manually having to delete all the characters in front of the @! It would have taken forever.

Sorry T. Valko, I used hiker95's macro because that was the one I saw first.

Brilliant! Thank you both so much for taking the time to solve my problem.

Have a great day!

I was thinking of doing an Edit>Replace and then a Text to Columns.

I actually had the reply posted but I deleted it because it was too vague not knowing what version of Excel was involved.

All's well that ends well!

 

[hiker95]

Epe,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.