If Statement Using Wild Cards

[Guest]

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!

 

[Guest]

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.

 

[Guest]

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

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


Good Luck

 

[Bob Umlas]

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

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!

 

[Paul Sheppard]

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

Don't know about wildcards, but this would work >

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

 

[Guest]

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: