Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula error
Hi
I cannot get this formula to work: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN",IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))=35,(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))<=1859),VALUE(TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN")) when I put it into a cell it just shows up as text. I formatted the cell as General and still nothing The formula auditor says it is a constant. the formula is supposed to take text like this: BGN/17/48 and return the number after the last / character can anyone help ? J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula error
After you format as General, try pressing F2 and then Enter.
HTH, Paul -- "ScoobyDoo" wrote in message ... Hi I cannot get this formula to work: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN",IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))=35,(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))<=1859),VALUE(TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN")) when I put it into a cell it just shows up as text. I formatted the cell as General and still nothing The formula auditor says it is a constant. the formula is supposed to take text like this: BGN/17/48 and return the number after the last / character can anyone help ? J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula error
On Jul 10, 6:03*pm, "PCLIVE" wrote:
After you format as General, try pressing F2 and then Enter. HTH, Paul -- "ScoobyDoo" wrote in message ... Hi I cannot get this formula to work: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN",IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))=35,(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))<=1859),VALUE(TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN")) when I put it into a cell it just shows up as text. I formatted the cell as General and still nothing The formula auditor says it is a constant. the formula is supposed to take text like this: BGN/17/48 and return the number after the last / character can anyone help ? J- Hide quoted text - - Show quoted text - Thanks... Got it working |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula error
Does this array-entered** formula do what you are attempting to do with the
formula you posted? =IF(ISNUMBER(-FIND("/",A1)),MID(A1,MAX((MID(A1,ROW($1:99),1)="/")*ROW($1:99))+1,99),"") ** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. Rick "ScoobyDoo" wrote in message ... Hi I cannot get this formula to work: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN",IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))=35,(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))<=1859),VALUE(TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN")) when I put it into a cell it just shows up as text. I formatted the cell as General and still nothing The formula auditor says it is a constant. the formula is supposed to take text like this: BGN/17/48 and return the number after the last / character can anyone help ? J |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula error
For the archives....
I omitted some absolute references that are necessary if the formula ever needs to be copied down... =IF(ISNUMBER(-FIND("/",A1)),MID(A1,MAX((MID(A1,ROW($1:$99),1)="/")*ROW($1:$99))+1,99),"") However, here is normally entered formula that produces the same results... =IF(ISNUMBER(-FIND("/",A1)),TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"/",REPT("/",99)),99),"/"," ")),"") Rick "Rick Rothstein (MVP - VB)" wrote in message ... Does this array-entered** formula do what you are attempting to do with the formula you posted? =IF(ISNUMBER(-FIND("/",A1)),MID(A1,MAX((MID(A1,ROW($1:99),1)="/")*ROW($1:99))+1,99),"") ** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. Rick "ScoobyDoo" wrote in message ... Hi I cannot get this formula to work: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN",IF(and(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))=35,(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))<=1859),VALUE(TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",100)), 100))),"UNKNOWN")) when I put it into a cell it just shows up as text. I formatted the cell as General and still nothing The formula auditor says it is a constant. the formula is supposed to take text like this: BGN/17/48 and return the number after the last / character can anyone help ? J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula returns error; abbreviating the formula | Excel Discussion (Misc queries) | |||
Formula Error-Error Message | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions | |||
Formula error with Mac resulting in '#NAME' error | Excel Programming |