How to get an email from a cell which contains more records?
For example, A1 cell has the following value:
8242663603,"nickname","mail@gmail.com"
I only need an email from this record in cell B1:
mail@gmail.com
and email always comes last
Asked
Active
Viewed 170 times
-1
-
1Is the email always last? Is it always three "items"? – Andreas Nov 11 '19 at 13:01
-
3Your current post is lacking some information on what you have researched/tried yourself. Please refer to [ask] a question with a [mcve] and edit your question accordingly. – JvdV Nov 11 '19 at 13:01
-
yes email always comes last, sorry that i didn't wrote that – Ruz Nov 11 '19 at 13:14
-
1You can use flashfill. Just type the first email in B1 and select B1 to B(endcell) and use flashfill https://i.stack.imgur.com/3kVsH.png – Andreas Nov 11 '19 at 13:26
-
@Andreas mine did not work - the second row grabbed the first item since it was an email – Marcucciboy2 Nov 11 '19 at 13:46
-
https://stackoverflow.com/a/350339/2727437 – Marcucciboy2 Nov 11 '19 at 13:48
-
1@Marcucciboy2 Then you need to manually type a few more before using the flashfill. – Andreas Nov 11 '19 at 13:59
2 Answers
0
Assuming that the formatting is always the same as your example:
=MID($A1,FIND(""",""",$A1)+3,LEN($A1)-FIND(""",""",$A1)-3)
This selects the text between "," and the end of the text minus 1 character.
Gravitate
- 2,885
- 2
- 21
- 37
-
-
No problem. If it solved your problem, please accept the answer by clicking the tick on the left hand side of the answer. – Gravitate Nov 12 '19 at 09:07
0
Assuming there is always at least 1 " before the emailaddress:
=MID(A1,FIND("~",SUBSTITUTE(A1,"""","~",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))-1))+1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"""","~",LEN(A1)-LEN(SUBSTITUTE(A1,"""",""))-1))-1)
dreojs16
- 109
- 12
-
Why have you added all the `SUBSTITUTE` functions? Other than that, it appears to be identical to my answer. What benefit does this offer over my, shorter solution? – Gravitate Nov 12 '19 at 14:24
-
@Gravitate This way the text before the emailaddress and its formatting is of no importance, as long as it contains at least 1 ". In your solution the formatting should always follow the same pattern with _something_ **,"** _something_ **","**_something_ – dreojs16 Nov 18 '19 at 12:37
-
Hmm. I see but I partially disagree. As long as `","` appears immediately before the email address, the pattern doesn't matter. Your answer still requires that the email address is in the last position (the same as mine does). The only difference I can see is that yours still works if `","` appears earlier in the string, where as mine would break. BUT if a `~` appeared in the string, that would break yours, where as mine would still work. – Gravitate Nov 19 '19 at 09:24