1

I want to remove data from multiple cells in a spreadsheet using Excel 2010, so I only have the email address left.

They are in the current format:

M: 04545 545 545 e: [email protected] h: 01187 475 454

Is there any easy way to do this?

Thanks

3 Answers 3

3

Assumption:

You don't have space in any of email id(I doubt you will.)

Solution:

Use a feature called text to columns in data tab excel(2007/10).

Use "Space" as custom delimiter.(It is pretty self-explanatory and shows you a preview as well).

That will get all the email addresses in single call and rest of the data in other columns. Then just delete the un-needed columns...

2

If its a one off, just use Find/Replace in two steps

Replace M:* e:  with nothing (note the space on the end)
Replace  h: * with nothing (note the leading space)

1

Assumptions

  • The A column contains the data (otherwise change the references in the below formula)
  • All cells in question contain the text e: before and h: after the e-mail

Strategy

  • In a new column, create a formula that extracts the e-mail
  • If warranted, copy/paste the new column using Paste Values and remove the original columns

Formula

=MID(A1;FIND("e: ";A1)+3;FIND("h: ";A1)-FIND("e: ";A1)-4

MID retrieves a substring of the original cell using a start index and a specified number of characters. FIND is used to get the index of the e: and h: markers.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .