If Statement Using Wild Cards

I am trying to write an If statement that uses wild cards, but it is not
working. My formula looks like =IF(A6="109*",85,IF(A6="110*",75,0)). A6 could
have a value of 109: Consumer Construction 1-4 Family Residential and I don't
want to have to type out the whole value. Is there a way to do this? Thanks!

An alternate would be to set up another column which takes the first three
characters out of your A column (assuming the code you want is always the 1st
3 characters). You can do this by using the LEFT command ( see
http://www.auditexcel.co.za/text.html to see how to use it).

Now you can write a normal IF formula and for that matter sort or filter on
the new column. That way it is also easier to see what is happening in the
spreadsheet instead of having increasingly complex formula in one cell.

Hope this is what you were looking for.

"Jasmine" wrote:

> I am trying to write an If statement that uses wild cards, but it is not
> working. My formula looks like =IF(A6="109*",85,IF(A6="110*",75,0)). A6 could
> have a value of 109: Consumer Construction 1-4 Family Residential and I don't
> want to have to type out the whole value. Is there a way to do this? Thanks!

Change A6="109*"
to
LEFT(A6,3)="109"

Change A6="110*"
to
LEFT(A6,3)="110"


Good Luck
--
Gary's Student


"Jasmine" wrote:

> I am trying to write an If statement that uses wild cards, but it is not
> working. My formula looks like =IF(A6="109*",85,IF(A6="110*",75,0)). A6 could
> have a value of 109: Consumer Construction 1-4 Family Residential and I don't
> want to have to type out the whole value. Is there a way to do this? Thanks!

Try:
=IF(LEFT(A9,3)="109",85,IF(LEFT(A6,3)="110",75,0))

"Jasmine" <[email protected]> wrote in message
news:[email protected]...
> I am trying to write an If statement that uses wild cards, but it is not
> working. My formula looks like =IF(A6="109*",85,IF(A6="110*",75,0)). A6
could
> have a value of 109: Consumer Construction 1-4 Family Residential and I
don't
> want to have to type out the whole value. Is there a way to do this?
Thanks!

Hi Jasmine,

Try this UDF if you really need wildcards because of undefined length of
cell values:

Public Function wcif(s1 As String, s2 As String) As Boolean
wcif = (s1 Like s2 & "*")
End Function

Apply like
=IF(WCIF(A6,"109"),85,IF(WCIF(A6,"110"),75,0))

Regards,
Stefi

„Jasmine” ezt �*rta:

> I am trying to write an If statement that uses wild cards, but it is not
> working. My formula looks like =IF(A6="109*",85,IF(A6="110*",75,0)). A6 could
> have a value of 109: Consumer Construction 1-4 Family Residential and I don't
> want to have to type out the whole value. Is there a way to do this? Thanks!