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

	A​	B​	C​
1​	amit.dhamija@rediffmail.com	rediffmail.com	rediffmail
2​	aa.bb.cc@inter.nl.net	inter.nl.net	inter
3​	John.Smith@example.com	example.com	example
4​	simple@example.com	example.com	example
5​	very.common@example.example.com	example.example.com	example
6​	disposable.style.email.with+symbol@example-example.com	example-example.com	example-example
7​	other.email-with-hyphen@~example-test-example.com	~example-test-example.com	~example-test-example

Sheet: Sheet1

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

RAW		RAW
amit.dhamija@rediffmail.com		rediffmail
aa.bb.cc@inter.nl.net		inter
John.Smith@example.com		example
simple@example.com		example
very.common@example.example.com		example
disposable.style.email.with+symbol@example-example.com		example-example
other.email-with-hyphen@~example-test-example.com		~example-test-example

[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.