//some 3rd party need geo

Excel: datasort or something similar

  exdragon 23:19 23 Jan 03
Locked
  exdragon 23:19 23 Jan 03

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 03

Not easily. I stand to be corrected.

  exdragon 23:30 23 Jan 03

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 03

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 03

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 03

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 03

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 03

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
Next
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:

=FIND("@",D3)

in c1 type
=findspaceback(D3,D5)

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

in e1 type
=trim(MID(D3,D6,D7-D6))

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 03

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

Next

findspaceback = a

End Function

  24dragon 10:57 24 Jan 03

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.

What is Google Allo? What is Google Duo? Google Allo UK release date rumours and features: Google…

1995-2015: How technology has changed the world in 20 years

These clever designs help visualise a complex intelligence tool

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…