Extracting some data from a string in a cell

[AMIT2179]

One more help needed. In one column i have multiple email addresses of different length. For eg: amit.dhamija@rediffmail.com. How can i extract value after "@" and before "." which in this case is "rediffmail" ??

 

[strooman]

Will this work for you?

Excel 2016 Professional(Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]amit.dhamija@rediffmail.com[/td][td]rediffmail.com[/td][td]rediffmail[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]aa.bb.cc@inter.nl.net[/td][td]inter.nl.net[/td][td]inter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]John.Smith@example.com[/td][td]example.com[/td][td]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]simple@example.com[/td][td]example.com[/td][td]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]very.common@example.example.com[/td][td]example.example.com[/td][td]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]disposable.style.email.with+symbol@example-example.com[/td][td]example-example.com[/td][td]example-example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]other.email-with-hyphen@~example-test-example.com[/td][td]~example-test-example.com[/td][td]~example-test-example[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

B1=MID(A1;FIND("@";A1)+1;LEN(A1))
C1=LEFT(B1;FIND(".";B1;FIND(".";B1))-1)

 

[alansidman]

Using Power Query, here is the Mcode for that

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter @" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter period" = Table.SplitColumn(#"Split Column by Delimiter @", "Column1.2", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Removed Unessary Columns" = Table.RemoveColumns(#"Split Column by Delimiter period",{"Column1.1", "Column1.2.2"})
in
    #"Removed Unessary Columns"

Click on the link in my signature to better understand Mcode and Power Query. It has been part of Excel since version 2010.

 

[AMIT2179]

Thanks but this gives an error somehow....Doesnt work

 

[sandy666]

another way with Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]RAW[/td][td][/td][td=bgcolor:#70AD47]RAW[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]amit.dhamija@rediffmail.com[/td][td][/td][td=bgcolor:#E2EFDA]rediffmail[/td][/tr]

[tr=bgcolor:#FFFFFF][td]aa.bb.cc@inter.nl.net[/td][td][/td][td]inter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John.Smith@example.com[/td][td][/td][td=bgcolor:#E2EFDA]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td]simple@example.com[/td][td][/td][td]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]very.common@example.example.com[/td][td][/td][td=bgcolor:#E2EFDA]example[/td][/tr]

[tr=bgcolor:#FFFFFF][td]disposable.style.email.with+symbol@example-example.com[/td][td][/td][td]example-example[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]other.email-with-hyphen@~example-test-example.com[/td][td][/td][td=bgcolor:#E2EFDA]~example-test-example[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.TransformColumns(Source, {{"RAW", each Text.BetweenDelimiters(_, "@", "."), type text}})
in
    Between[/SIZE]

btw. this is NOT vba!

 

[strooman]

Thanks but this gives an error somehow....Doesnt work

Replace the semi colon ( with a comma (,).

 

[AMIT2179]

ohh ok. So how should i use it? I am unsure. Isnt there a formulae (like the one you gave before)? As its easy to implement.

 

[alansidman]

ohh ok. So how should i use it? I am unsure. Isnt there a formulae (like the one you gave before)? As its easy to implement.

Would help it you indicated who you are referring this comment to.

 

[Rick Rothstein]

Isnt there a formulae (like the one you gave before)? As its easy to implement.

Give this formula a try...

=MID(LEFT(A1,FIND(".",A1,FIND("@",A1))-1),FIND("@",A1)+1,LEN(A1))

 

[AMIT2179]

Works perfectly. Thanks much.