Extract text between inverted commas

Question

I have got a list of email addresses  in the following format in a spreadsheet. All email addresses are between inverted commas. How can I extract just the email addresses? See below...

If possible it would be awesome to also be able to get the first and last name in separate columns beside the email address. You will notice the first name appears in the line below the last name.

Appreciate any help!

Pozzan "******@srw.com.au"
Kane
Watson "******@gmail.com"
Garry
Baker "******@simon.com.au"
Chris
Rose "******@endraulic.co.nz"
Blair
Sargison "******@live.com.au"
Caitlyn
Broderick "******@yahoo.com.au"
Matt
Dols "******@hotmail.com"
Matt
Moore "******@delnorth.com"
Scott

Answer 1

With Pozzan "null<at>null.com" in A1 and Kane in A2, put this in an unused column to the right,

=IF(ISNUMBER(FIND(CHAR(64),A1)),LOWER(TRIM(MID(SUBSTITUTE(A1,CHAR(34), REPT(CHAR(32),99)),99,99))),"")

In a column beside that, use this,

=IF(ISNUMBER(FIND(CHAR(64),A1)),A2&CHAR(32)&TRIM(LEFT(SUBSTITUTE(A1,CHAR(34),REPT(CHAR(32),99)),99)),"")

Then fill down as necessary. Copy the results and Paste Special, Values to get the values from the formulas and sort or filter them to remove the blank spaces.

Answer 2

With data starting in A1, and first name in A2

email:      =TRIM(MID(SUBSTITUTE($A1,"""",REPT(" ",255)),255*2-254,255))

Last name:  =IFERROR(LEFT(A1,FIND("""",A1)-1),"")

First Name: =IF(ISNUMBER(FIND("""",A1)),A2,"")

Select and fill down as far as required.

Answer 3

That worked perfectly :) Thanks!