Use wildcard in function

kamazoo · Nov 28th 2004

I have a long list of addresses that are organised like this:

Company
Road
Zip code
Phone number
Fax number
[email protected]
http://www.company.com

I would like to pull out all the email addresses and I have therefore been trying to write a formula looking like this : =IF(A1="*@*"; A1).

I was hoping that the * would work as a wildcard so that all fields containing the @ sign would be output in my resulting column. However it does not seem like I can use wildcards in a funtion like this. Can anyone help?

lasw10 · Nov 28th 2004

Re: Use wildcard in function

just use FIND

eg

=IF(ISERROR(FIND("@",A1)),"",A1)

kamazoo · Nov 28th 2004

Re: Use wildcard in function

when I copy this code into the Excel sheet I get an error and is not allowed to continue.

What am I doing wrong ?

**Batman** · Nov 28th 2004

Re: Use wildcard in function

Hi kamazoo,

I tried several ways of pasting that formula into a worksheet and I managed to get an error in 2 different circumstances

(1) I pasted into cell A1, to get a circular reference

(2) I pasted into a cell other than A1 then changed A1 to contain a formula
=D1+E1&"@"
This bombed me out of Excel, although when I went back in and tried entering that formula in A1 before I pasted in the Find formula it worked OK.

Perhaps you could give a few more details about the data in cell A1 and the error message you are getting.

By the way, welcome to the ozgrid forum.

Regards,
Batman.

Dave Hawley · Nov 29th 2004

Re: Use wildcard in function

Hi kamazoo

Welcome to ozgrid.com

Another, which might be better suited, is to use Data>Filter>Advanced Filter

If the list is in A1:A1000 and A1 is a heading (which it should be).

In cell B1 place an exact copy of the heading, i.e =A1

In B2 enter *@*

Select A1:A1000 and go Data>Filter>Advanced Filter and use B1:B2 as the Criteria range.

Check Copy to another location and choose a cell where the new list should start (Copy to and click Ok.

kamazoo · Nov 29th 2004

Re: Use wildcard in function

Hi Batman and Dave,

thanks for the welcome.

Batman's suggestion still gives me an error message (it just says "The formula you typed contains an error"). It tried to paste it into a blank worksheet in B1, but with no luck. Can that be due to different versions of Excel ? (I run Excel 2002).

However, I tried Dave's suggestion and that works great. So thanks very much for the tip - I can get on with my work now

Dave Hawley · Nov 29th 2004

Re: Use wildcard in function

Batman's function does work and I have tested in on Excel 2002. Just copy it from his post and select any cell (other than A1) then click in the Formula bar and Crtl+V and Enter.

Use wildcard in function

Participate now!

Share