Wildcards in formulae

[Guest]

Hello,

Im wondering if its possible for formulae to contain wild cards.

e.g.

i have a list of invoice numbers, some have LDN at the start some dont. What
i would like to do is make an IF formula which will find the invoices that
dont have LDN at the start. i was hoping that
'=if(a1=LDN*,a1,concatenate("LDN",a1))' would work but needless to say it
doesnt.

What, if anything replaces that * so i can search for part entires in cells?

Many thanks,

Rikki

 

[Guest]

=IF(LEFT(A1,3)="LDN",A1,"LDN" &A1)

 

[Guest]

If it is always 3 characters on the left that you are looking for then use
the LEFT function as per the following:-

=IF(LEFT(A1,3)<>"LDN","LDN"&A1,A1)

Note: The ambersand (&) can be used in lieu of the concatenate function and
is easier.

Regards,

OssieMac

 

[Guest]

Thanks chaps.

As a follow up, if the LDN bit was anywhere in the cell, not just on the
left, how would the formula work then?

 

[Guest]

If LDN wasn't at the beginning, what result would you want?

e.g 123LDN456 , how would it appear in the non-LDN string 987654?

 

[Guest]

=IF(ISNUMBER(FIND("LDN",A1)),A1,"LDN" &A1)

This will check if LDN exists: if NOT, it still adds to the front of the
invoice number.

 

[Guest]

Thanks for that, it was more of an afterthought question, not related to my
inital query.

 

[Dave Peterson]

And if you wanted to really use wild cards...

Starts with LDN
=if(countif(a1,"LDN*")>0, ....

or ends with LDN
=if(countif(a1,"*LDN")>0, ....

or contains LDN
=if(countif(a1,"*LDN*")>0, ....

 

[Guest]

="LDN"&SUBSTITUTE(A1,"LDN","")