How to extract email addresses to new column

  • Thread starter Thread starter Inquirer
  • Start date Start date
I

Inquirer

I have a large spreadsheet with email addresses lumped together in the
same column as phone numbers. I am trying to extract the email
addresses and place them in a new column within the same worksheet.

I don't have much of a background in setting up macros or formulas.

Any suggestions?
 
Can you post some examples of the fields that are lumped together?
Maybe there's always a space between the two pieces of data and you can
split it that way?

Thanks,
Johnny
 
if you want to use formulas, not VBA, and your addresses and phone numbers
are separated by a single space try this (assuming your list is in column "A")

in cell "B1" enter the formula

=LEFT(A1,FIND(" ",A1)-1)

in cell "C1" enter

=MID(A1,FIND(" ",A1)+1,LEN(A1)-LEN(B1))

then select cells "B1:C1" and drag (copy) them down to the end of your list
 
Johnny said:
Can you post some examples of the fields that are lumped together?
Maybe there's always a space between the two pieces of data and you can
split it that way?

Thanks,
Johnny
Click to expand...


This is a sammple of what the information in the cell contains....

555-778-3230 cell 555-252-5972 (e-mail address removed)

555-676-5332 (e-mail address removed)

555-846-5352 work 555-254-5505 home 555-668-6321 cell
(e-mail address removed) (e-mail address removed)

555-761-1436 home 555-216-1286 cell (e-mail address removed)

555-682-5533 work 555-642-7987 cell 555-867-2592 home
(e-mail address removed)
 
Try using the Text to Column function under the Data menu. Choose
delimited and use a space as the delimiter. Otherwise, I would use a
formula as suggested by another poster. Finally, if that doesn't work,
you might try using Regular Expressions in VBScript. I won't go into
that if the other two ideas work.

Thanks,
Johnny
 
Back
Top