| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use of wildcards | Excel Worksheet Functions | |||
| Wildcards in RTD | Excel Discussion (Misc queries) | |||
| wildcards in vba | Excel Discussion (Misc queries) | |||
| Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
| VBA Wildcards - HELP! | Charts and Charting in Excel | |||
Hybrid Mode
