Excel: datasort or something similar

  exdragon 23:19 23 Jan 2003
  exdragon 23:19 23 Jan 2003

And another one, please!

If a list contains a mixture of email addresses, company names, ordinary names and addresses (not in columns - just typed in across the rows) is there a way to extract just the email addresses so they can be used for a mass emailing in OE6?

  VoG™ 23:23 23 Jan 2003

Not easily. I stand to be corrected.

  exdragon 23:30 23 Jan 2003

There isn't anyway to identify the @ sign in the email address is there? With one of your complicated statements? Mind you, if you don't know, who will??

This is a challenge, chaps!

  VoG™ 00:19 24 Jan 2003

You can use "Data/Text to columns" to break down an "address" to its components; e.g. if you had a cell with

VoG, pcadvisor.co.uk

you could easily split that into two components.

HOWEVER if the information is "random" this will not sort all the e-mail addresses into one column, which is what you need.

It depends on how large your problem is. If it is up to, say, a hundred rows then it is not too difficult to do this. Copy the column with the addresses etc. to a spare column, select the list in that column and data/text to columns. Delete cells that are unnecessary until all e-mails are in one column. Let's say that is Column E. Then in F1 enter

=HYPERLINK("mailto:" & E1)

and copy down. This will give you a series of clickable links.

  VoG™ 00:34 24 Jan 2003

Yes there is but how many characters to count back from @ and forward? Formidable... could be done but not cost effective unless you have thousands of rows; AND presumably you only need to do this once.

  jazzypop 03:26 24 Jan 2003

I bow to VoG™'s expertise in all matters to do with Excel, but if all you want to do is extract the email addresses, how about...

1. Copy and paste the data to a fresh sheet (so you leave the original intact.

2. Highlight the cells containing data

3. Go to Data > Text to Columns (as VoG™ said earlier)

4. In Step 1, choose delimited

5. In Step 2, tick the 'space' box

6. In Step 3, just click Finish

7. You will end up with each piece of data (name, address etc) in a separate column. At least now you have each email address isolated into a cell of its own.

The first 4 or 5 columns will almost certainly not contain the email address, so can be deleted. To be safe, highlight the columns and use the Edit > Find facility to search for the @ symbol.

You can then either do some manual drag and drop to place the emails in nice neat columns, or if there is a very large amount of data, use a Lookup formula to copy any cell containing @ to another sheet.

Not elegant at all, but it might save you a fair bit of work.

Maybe VoG™ can suggest a neat way of moving each cell containing an @ symbol to another sheet, now that they are isolated in their individual cells?

  jazzypop 03:28 24 Jan 2003

Doh! I just realised that I took 3 times as long to say what VoG™ said in his second post. Now where's that 'Delete post' button :)

  cherria 10:31 24 Jan 2003

I assume you at least have a space between each element in which case.

Press Alt+F11 to open a VBA editor type the following code:

Function findspaceback(mycell As Range, start As Integer) As Integer

Dim pos As Integer
content = mycell.Value
For a = start To 1 Step -1
If Mid(content, a, 1) = " " Then Exit For
findspaceback = a
End Function

this function will find the position of the first space in a text string looking backwards from a specified position.

Then in the spreadsheet if your first text string is in cell A1 in cell B1 type the following formula:


in c1 type

in d1 type
=FIND(" ",D3,D5)

in e1 type

in e1 you should have your e-mail address.

this only works if you have a space at the start and end of hte e-mail address.

  cherria 10:37 24 Jan 2003

Sorry, th function has come out badly, should read:

Function findspaceback(mycell As Range, start As Integer) As Integer

Dim pos As Integer

content = mycell.Value

For a = start To 1 Step -1

If Mid(content, a, 1) = " " Then Exit For


findspaceback = a

End Function

  24dragon 10:57 24 Jan 2003

I'm lost - never done this before. When I type in
Function findspaceback(mycell As Range, start As Integer) As Integer , it's looking for a bracket after the first As Range - if I put one in, it accepts it but then wheat do I do with the rest of the line?

Do I need to select anything on the spreadsheet first and do I actually type in mycell, or is it a reference to a cell on the ss?

Sorry to be thick - if you've lost the will to live, I quite understand!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Adobe shows still-in-development tools, including automatically colourising black-and-white photos

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?