Remove specifc text starting with @

Y

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
852
Hi everybody
I thank everybody who is participating in this great forum

I have text data
Have one of four cases:
1- text that hasn't the character @ at all
2- text that has one occurrence of the character @
3- text with two occurrences
4- text with three occurrences

As for case 1 the data should remain as it is
As for case 2 the string starting at @ should be removed
As for case 3 the string starting at the second occurrence of the character @ should be removed
As for case 4 the string starting at the third occurrence should be removed

I hope it is clear
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi Yasser

Try:

=LEFT(A1,LOOKUP(2^15,IF({1,0},LEN(A1),FIND("|",SUBSTITUTE(A1,"@","|",LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))))-1)))
 
Upvote 0
YasserKhalil said:
Very good for the last occurrence of the character...
But it doesn't solve my problem
Click to expand...

Hi again

Your post is not informative.

You should write something like:

---------------

This does not solve my problem because

Example 1: with the string "xxx" you formula returns "yyy" and should return "zzz"

Example 1: with the string "aaa" you formula returns "bbb" and should return "ccc"

etc.

---------------------

This way I would be able to understand what was wrong and try to correct it.

Now, you just say "it doesn't work" but you don't say why or how and so there's nothing I can do.
 
Upvote 0
Hi again

I applied the formula to your 4 examples and this is what I got:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">How are you?</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">How are you?</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellodear@www.ggf.com</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellodear</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellomyfriend@yahoo.com@www.gg.com</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellomyfriend@yahoo.com</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellomydear@gmail.com@www.gg.com@gfgf</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">hellomydear@gmail.com@www.gg.com</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>


So it seems the first 3 cases are ok. It's the fourth case that is not as you want

YasserKhalil said:
4-hellomydear@gmail.com@www.gg.com@gfgf
Should be : ellomydear@gmail.com
Click to expand...


Now in your first post you wrote:

YasserKhalil said:
...
4- text with three occurrences
...
As for case 4 the string starting at the third occurrence should be removed
Click to expand...

So, your 4th example, the string starting at the third occurrence of @ is bolded

hellomydear@gmail.com@www.gg.com@gfgf


and the result should be

hellomydear@gmail.com@www.gg.com

Which is the result of my formula.

Everything seems to be working ok.

Please explain.
 
Upvote 0
YasserKhalil said:
OK Sir
Here's examples of the four instances
1- How are you?
Should be as it is : How are you?

2- hellodear@www.ggf.com
Should be : hellodear

3- hellomyfriend@yahoo.com@www.gg.com
Should be : hellomyfriend@yahoo.com

4-hellomydear@gmail.com@www.gg.com@gfgf
Should be : ellomydear@gmail.com

I hope it is clear now Mr. pgc01
Click to expand...

The suggested formula, although fancy:laugh:, would yield those outcomes except for the latter. Another formula, also a bit fancy but somewhat usual...
Code: 
=LEFT(A2,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
    LEN(A2),FIND("#",SUBSTITUTE(A2,"@","#",
      LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))))-1)))

The specs for the latter you provided in your initial post would want:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=64>hellomydear@gmail.com@www.gg.com</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
It's the fourth case that is not as I want
I'm sorry for confusion

I really want to remove the text starting at the second occurrence if there are three occurrences of it

hellomydear@gmail.com@www.gg.com@fghfgh

should be :
hellomydear@gmail.com
 
Upvote 0
YasserKhalil said:
It's the fourth case that is not as I want
I'm sorry for confusion

I really want to remove the text starting at the second occurrence if there are three occurrences of it

hellomydear@gmail.com@www.gg.com@fghfgh

should be :
hellomydear@gmail.com
Click to expand...

Tweaking a bit what we have:
Code: 
=LEFT(A2,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
    LEN(A2),FIND("#",SUBSTITUTE(A2,"@","#",
      MIN(2,LEN(A2)-LEN(SUBSTITUTE(A2,"@","")))))-1)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
First instance of consecutive upper case letters in a string
Replies
2
Views
475
Eric W
Eric W
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
665
felixstraube
F
J
  • Question Question
Crazy Formula to VBA.
Replies
2
Views
212
jvoss
J
J
  • Question Question
VBA Search box and button code
Replies
0
Views
271
jlcodes
J
S
  • Question Question
Retrieving data by dynamic address
Replies
1
Views
84
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,551
Messages
6,179,473
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top