ExcelBanter - Wildcards in formulae

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Wildcards in formulae (https://www.excelbanter.com/excel-discussion-misc-queries/152205-wildcards-formulae.html)

Chiccada

Wildcards in formulae

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

Toppers

Wildcards in formulae

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

"Chiccada" wrote:

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

OssieMac

Wildcards in formulae

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

"Chiccada" wrote:

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

Chiccada

Wildcards in formulae

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?

"OssieMac" wrote:

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

"Chiccada" wrote:

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

Toppers

Wildcards in formulae

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?

"Chiccada" wrote:

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?

"OssieMac" wrote:

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

"Chiccada" wrote:

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

Toppers

Wildcards in formulae

=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.

"Toppers" wrote:

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?

"Chiccada" wrote:

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?

"OssieMac" wrote:

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

"Chiccada" wrote:

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

Chiccada

Wildcards in formulae

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

"Toppers" wrote:

=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.

"Toppers" wrote:

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?

"Chiccada" wrote:

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?

"OssieMac" wrote:

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

"Chiccada" wrote:

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

Dave Peterson

Wildcards in formulae

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, ....

Chiccada wrote:

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

--

Dave Peterson

Teethless mama

Wildcards in formulae

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

"Chiccada" wrote:

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

All times are GMT +1. The time now is 10:41 AM.