Extracting some data from a string in a cell

AMIT2179

New Member
Joined
Nov 2, 2011
Messages
43
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" ??
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
Will this work for you?

Excel 2016 Professional(Windows) 64 bit
A
B
C
1
amit.dhamija@rediffmail.comrediffmail.comrediffmail
2
aa.bb.cc@inter.nl.netinter.nl.netinter
3
John.Smith@example.comexample.comexample
4
simple@example.comexample.comexample
5
very.common@example.example.comexample.example.comexample
6
disposable.style.email.with+symbol@example-example.comexample-example.comexample-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)
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,426
Office Version
2019
Platform
Windows
Using Power Query, here is the Mcode for that

Code:
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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
another way with Power Query

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

Code:
[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!
 
Last edited:

AMIT2179

New Member
Joined
Nov 2, 2011
Messages
43
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,426
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,716
Messages
5,446,111
Members
405,381
Latest member
James W

This Week's Hot Topics

Top