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" ??
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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