Excel 2007 : Need to remove symbol @ and text after it in more then 1000 rows.

**umarshafiq** · 05-08-2012, 06:24 PM

hi to all members, i am a newbie in excel some help needed, i have an excel sheet of more then 1000 rows, i want to remove the symbol @ and the text after it,
like
username@domain.com i need text username and rest should be eliminated from all the rows.

and needs the result in new column,
i tried the formula
=IF(ISNUMBER(FIND("@",B2)),LEFT(A1,FIND("@",B2)))
but it works only for one cell, how can i modify this formula for the whole column. Or multiple cells.

Any help would be appreciated. Or anyone can do this for me. file attached

Thanks in advanced...

**vandan_tanna** · 05-08-2012, 06:27 PM

=IF(ISNUMBER(FIND("@",B2)),LEFT(A1,FIND("@",B2)))
Should this be B2?

**abousetta** · 05-08-2012, 06:29 PM

Ctrl + H

Find: @*
Replace: [leave this blank]

Replace All with a click of a buton

**umarshafiq** · 05-08-2012, 06:29 PM

Originally Posted by vandan_tanna

=IF(ISNUMBER(FIND("@",B2)),LEFT(A1,FIND("@",B2)))
Should this be B2?

yes this should be B2, but the issue is that this works only for one cell, i need to done it for nearly 1000 cells, how can i do this.
Thanks for your help

**umarshafiq** · 05-08-2012, 06:31 PM

Originally Posted by abousetta

Ctrl + H

Find: @*
Replace: [leave this blank]

Replace All with a click of a buton

Thank you very much....this works....

**abousetta** · 05-08-2012, 06:34 PM

Thanks for the feedback.

Good luck.

abousetta

**vandan_tanna** · 05-08-2012, 06:34 PM

=IF(ISNUMBER(FIND("@",B2)),LEFT(B2,FIND("@",B2)-1))

add -1 to get rid of @

it should work for all cells. Are you copying formula down properly?

**umarshafiq** · 05-08-2012, 06:40 PM

Originally Posted by vandan_tanna

=IF(ISNUMBER(FIND("@",B2)),LEFT(B2,FIND("@",B2)-1))

add -1 to get rid of @

it should work for all cells. Are you copying formula down properly?

Thanks for help your vandan_tanna.... my problem is solved by using

Originally Posted by abousetta

Ctrl + H

Find: @*
Replace: [leave this blank]

Replace All with a click of a buton

Excel 2007 : Need to remove symbol @ and text after it in more then 1000 rows.

LinkBack

Thread Tools

Rate This Thread

Display

Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Re: [help-needed] Need to remove symbol @ and text after it in more then 1000 rows.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions