Extracting text from a string

Hi,

Hope this is descript enough. I work in IT, I am running a report to identify license usage. We have 3 companies using these licenses on the server. The only way to identify a user from a company is their email address. I would like to extract the word between at and the first dot so JohnDoe at company 1 dot com JaneSmith at company 2 dot com Steve stevenson at company 3 dot com

Email addresses are in Column I, the data will be moved to column J

So then I'd have a column with

Company 1
Company 2
Company 3

There are about 5200 licenses and I don't want to type this out manually for each one lol.


Thanks in advance.

Can be achieved quickly using Power Query/Get and Transform Data found on the Data Tab.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Try:

Formula:

Please Login or Register  to view this content.

Did you see post #4?

Try:

Formula:

Please Login or Register  to view this content.

A Power Query Solution

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thanks I appreciate the info but we use office 365. I don't believe Power Query has that functionality with O365.

Power Query is called Get and Transform Data in O365 and is found on the Data Tab. It has been part of Excel since version 2010 for those running on Windows.

Please upload a representative sample AND some manually calculated expected results.

In the meantime, does this do anything for you?


=FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(I2,"@","."),".","</B><B>")&"</B></A>","//B[position () = 2]")

copied down.

So the formula didn't work at first, it would give me the last name before the at symbol and when I copied it to the next cell it provided a Ref# error. So I adjusted the formula to [position () = 3] and it gave me the expected result. So I copied it to the next cell, changed the reference to I3 and it worked again, so then I double clicked the box in the bottom right of the cell and it copied, properly, the formula down 5200 rows. Identifying which companies are using how many licenses.


Seriously can't say thank you enough!!!! Hope you have a great weekend!!!

Thank you.

Just an FYI the formula works if the email is first dot last if say their email is lastfir then you have to manually edit the formula to change to position 2 again. AND if say they have first dot middle dot last then you have to manually change to position 4, other than that, it's great. There were only about 20 that had to be manually adjusted, far cry from the 5200 I was looking at.

It's pretty tough to second guess what may or may not be there depending on what you have removed for whatever reason.

Post desensitised samples of email addresses that truly represent what your data looks like. And include expected results. My expectation was that if you have company.company then you need company not company.company.

We're wasting a lot of time here for what should be a trivial request.

Grand. But next time, please upload a sample WITH expected results, right from the start.

Originally Posted by TMS

It's pretty tough to second guess what may or may not be there depending on what you have removed for whatever reason.

Sorry I had uploaded a spreadsheet, I didn't realize that I need to include expected manual entries, I literally have 12 or 13 posts here. I'm new, again. Sorry. I'll know for next time.

I'm still waiting to hear back from you in your earlier thread ...

You did tell us what you wanted ... but that wasn't what you wanted.

I would like to extract the word between at and the first dot so JohnDoe at company 1 dot com JaneSmith at company 2 dot com Steve stevenson at company 3 dot com

JohnDoe@company 1.com
JaneSmith@company 2.com
Steve stevenson@company 3.com

So, this should give you what I think you want.

Formula:

Please Login or Register  to view this content.

copied down
or just:

Formula:

Please Login or Register  to view this content.