IF Statement question

[John Corbin]

I have text like

xyz abc/12

I user this formula to retrieve the number after the /

=RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))

This works.. provided there is a number after the / or even an / in
the string I am looking at.

Thus if I have

xyz abc

the above formula returns #VALUE! I expect this

I put an IF condition in and the formula becomes

=IF(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/",""))))) < 999,(RIGHT(H2,LEN(H2)-
FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))),"UNKNOWN")

it returns UNKNOWN always, regardles of wether or not a number is
really in the place I am hoping it is.

What is wrong ?

 

[Ron Rosenfeld]

I have text like

xyz abc/12

I user this formula to retrieve the number after the /

=RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))

This works.. provided there is a number after the / or even an / in
the string I am looking at.

Thus if I have

xyz abc

the above formula returns #VALUE! I expect this

I put an IF condition in and the formula becomes

=IF(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/",""))))) < 999,(RIGHT(H2,LEN(H2)-
FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))),"UNKNOWN")

it returns UNKNOWN always, regardles of wether or not a number is
really in the place I am hoping it is.

What is wrong ?

The RIGHT function always returns a text string. While 12<999 evaluates to
TRUE, "12"<999 will evaluate to FALSE, and your IF statement will return
"UNKNOWN".

Of course, if you don't have a slash in your string, your second formula will
also return #VALUE!

You can change your formula to return the number if present and less than 999
by converting the results of your first RIGHT function to a numeric value:

=IF(--(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))) < 999,(RIGHT(H2,LEN(H2)-
FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))),"UNKNOWN")

Of course, your formula, even as modified, will still return the #VALUE! error
if there is no slash present.

There may be simpler ways to accomplish your goal, but I'm not sure what your
goal is. The simpler solution also depends on what the possible formatting of
your string might be.

--ron

 

[John Corbin]

The RIGHT function always returns a text string.  While 12<999 evaluates to
TRUE, "12"<999 will evaluate to FALSE, and your IF statement will return
"UNKNOWN".

Of course, if you don't have a slash in your string, your second formula will
also return #VALUE!

You can change your formula to return the number if present and less than999
by converting the results of your first RIGHT function to a numeric value:

=IF(--(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))) < 999,(RIGHT(H2,LEN(H2)-
FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))),"UNKNOWN")

Of course, your formula, even as modified, will still return the #VALUE! error
if there is no slash present.

There may be simpler ways to accomplish your goal, but I'm not sure what your
goal is.  The simpler solution also depends on what the possible formatting of
your string might be.

--ron- Hide quoted text -

- Show quoted text -

Thanks

I came to teh same conclusion and used a value function like thsi:

=IF(VALUE(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))) < 999,(RIGHT(H2,LEN(H2)-
FIND("*",SUBSTITUTE(H2,"/","*",LEN(H2)-
LEN(SUBSTITUTE(H2,"/","")))))),"UNKNOWN")

and you are right, I always get a number if the number comes after
the / but I gte #$VALUE! if no /. exists

so here is my goal....

In a text string of any length, I want to find the last occurance of /
and return the number that comes right after if it is less than 999.

 

[Ron Rosenfeld]

In a text string of any length, I want to find the last occurance of /
and return the number that comes right after if it is less than 999.

From what you write, I assume there can be multiple slashes.

What you didn't specify was what to return if no number less than 999 exists.

From a previous post, I assume you want to return the word "UNKNOWN".

I don't recall who first posted this technique. RagDyer last used a variation
of it, as best I recall:

=TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))

will return the last "word" in the string with the word separator being
defined, in this case, as a slash "/". If your maximum string length is
greater than 99, then change the 99's to that value.

Putting it together:

=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))),"UNKNOWN",
IF(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))<999,
TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99)),"UNKNOWN"))

will return the value if less than 999, or UNKNOWN.

If you want to return the value as a number, then:

=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))),"UNKNOWN",
IF(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))<999,
--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99)),"UNKNOWN"))

--ron

 

[John Corbin]

From what you write, I assume there can be multiple slashes.

What you didn't specify was what to return if no number less than 999 exists.

From a previous post, I assume you want to return the word "UNKNOWN".

I don't recall who first posted this technique.  RagDyer last used a variation
of it, as best I recall:

=TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))

will return the last "word" in the string with the word separator being
defined, in this case, as a slash "/".  If your maximum string length is
greater than 99, then change the 99's to that value.

Putting it together:

=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))),"UNKNOWN",
IF(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))<999,
TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99)),"UNKNOWN"))

will return the value if less than 999, or UNKNOWN.

If you want to return the value as a number, then:

=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))),"UNKNOWN",
IF(--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99))<999,
--TRIM(RIGHT(SUBSTITUTE(H2,"/",REPT(" ",99)),99)),"UNKNOWN"))

--ron

Great.. works like a charm.. much thanks

 

[Ron Rosenfeld]

Great.. works like a charm.. much thanks

You're welcome. Glad to help. Thanks for the feedback.
--ron