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

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

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

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

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