| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel insists on adding character to formula
Have a formula
What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel insists on adding character to formula
What is in the cell that might precede the ".124"?
If it's a number like 1.124 then the wildcards won't work. Wildcards don't work on numbers, they only work on text. If it's a text string like A.124 then try this: IF(COUNTIF(A1,"*.124*"),"yes" The squiggly brackets { } denote an array constant. Since you're only using a single criteria they're not needed. -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a formula What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel insists on adding character to formula
On Mar 31, 4:03*pm, "T. Valko" wrote:
What is in the cell that might precede the ".124"? If it's a number like 1.124 then the wildcards won't work. Wildcards don't work on numbers, they only work on text. If it's a text string like A.124 then try this: IF(COUNTIF(A1,"*.124*"),"yes" The squiggly brackets { } denote an array constant. Since you're only using a single criteria they're not needed. -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a formula What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. *If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre- Hide quoted text - - Show quoted text - Makes sense. . .Thank you, I'll try it in the AM, and report back. Pierre |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel insists on adding. . . Biff, it worked, but one question. . .
Since you have 2 criteria we can use the array constant again:
=IF(OR(COUNTIF(A1,"*"&{".124",".125"}&"*")),"Yes", "No") -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Biff: This did the trick; the data to be analyzed is text, and the source data looks something like: P00345.124ELE145 As it stands, I'm at the upper limit of IF statements, and could use one more, so I was wondering if I could combine into a single statement searching for: ..124, and .125? Right now, it looks like this, and works fine: IF(COUNTIF(A4,"*.124*"),"Yes",IF(COUNTIF(A4,"*.125 *"),"Yes",""))) Would it be something like =IF(OR(COUNTIF(A4,"*.124,.125*"),"Yes")) (It doesn't work, but I don't think it's phrased properly. Many thanks again, for your help and feedback. Pierre On Mar 31, 4:03 pm, "T. Valko" wrote: What is in the cell that might precede the ".124"? If it's a number like 1.124 then the wildcards won't work. Wildcards don't work on numbers, they only work on text. If it's a text string like A.124 then try this: IF(COUNTIF(A1,"*.124*"),"yes" The squiggly brackets { } denote an array constant. Since you're only using a single criteria they're not needed. -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a formula What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel insists on adding. . . Biff, it worked, but one question. . .
Or, this version which is a few keystrokes shorter:
=IF(OR(COUNTIF(A1,"*.12"&{4,5}&"*")),"Yes","No") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since you have 2 criteria we can use the array constant again: =IF(OR(COUNTIF(A1,"*"&{".124",".125"}&"*")),"Yes", "No") -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Biff: This did the trick; the data to be analyzed is text, and the source data looks something like: P00345.124ELE145 As it stands, I'm at the upper limit of IF statements, and could use one more, so I was wondering if I could combine into a single statement searching for: .124, and .125? Right now, it looks like this, and works fine: IF(COUNTIF(A4,"*.124*"),"Yes",IF(COUNTIF(A4,"*.125 *"),"Yes",""))) Would it be something like =IF(OR(COUNTIF(A4,"*.124,.125*"),"Yes")) (It doesn't work, but I don't think it's phrased properly. Many thanks again, for your help and feedback. Pierre On Mar 31, 4:03 pm, "T. Valko" wrote: What is in the cell that might precede the ".124"? If it's a number like 1.124 then the wildcards won't work. Wildcards don't work on numbers, they only work on text. If it's a text string like A.124 then try this: IF(COUNTIF(A1,"*.124*"),"yes" The squiggly brackets { } denote an array constant. Since you're only using a single criteria they're not needed. -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a formula What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel insists on adding. . . Biff, it worked, but one question. . .
Now, where do you wnat those pizzas sent?
Make 'em with thick crust, mushrooms, peppers, tomatoes and xtra cheese! Thanks for the feedback! -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Awesome!!! Now, where do you wnat those pizzas sent? Pierre On Apr 1, 12:08 pm, "T. Valko" wrote: Or, this version which is a few keystrokes shorter: =IF(OR(COUNTIF(A1,"*.12"&{4,5}&"*")),"Yes","No") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since you have 2 criteria we can use the array constant again: =IF(OR(COUNTIF(A1,"*"&{".124",".125"}&"*")),"Yes", "No") -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Biff: This did the trick; the data to be analyzed is text, and the source data looks something like: P00345.124ELE145 As it stands, I'm at the upper limit of IF statements, and could use one more, so I was wondering if I could combine into a single statement searching for: .124, and .125? Right now, it looks like this, and works fine: IF(COUNTIF(A4,"*.124*"),"Yes",IF(COUNTIF(A4,"*.125 *"),"Yes",""))) Would it be something like =IF(OR(COUNTIF(A4,"*.124,.125*"),"Yes")) (It doesn't work, but I don't think it's phrased properly. Many thanks again, for your help and feedback. Pierre On Mar 31, 4:03 pm, "T. Valko" wrote: What is in the cell that might precede the ".124"? If it's a number like 1.124 then the wildcards won't work. Wildcards don't work on numbers, they only work on text. If it's a text string like A.124 then try this: IF(COUNTIF(A1,"*.124*"),"yes" The squiggly brackets { } denote an array constant. Since you're only using a single criteria they're not needed. -- Biff Microsoft Excel MVP "Pierre" wrote in message ... Have a formula What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes" Excel "insists on placing a zero" before the dot in front of the 124 What it changes to by pressing enter: ,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes" I need the dot in the formula as to only check if the "124" immediately follows the dot, and not a 124 which may occur someplace within the cells contents. If I leave the zero in the formula, if does not return the correct answer. Is there a way around this apparent quirk? Thank you. pierre- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding up a particular character in a row | Excel Discussion (Misc queries) | |||
| Adding a character to a defined name with a formula or macro | Excel Discussion (Misc queries) | |||
| Excel 2000 insists on typing a formula | Excel Discussion (Misc queries) | |||
| Adding overscore to character in a cell in an Excell spreadsheet | Excel Discussion (Misc queries) | |||
| Adding a character to the beginning and end of txt in a cell | Excel Worksheet Functions | |||
Linear Mode
