Formula to establish the sequence number within a string?

[Roy Munson]

Hello can somebody help me with this part of my formula, I thought it was just a simple IF(Find(Right)) formula but it returns a #Value!!!

=IF(FIND(">",(RIGHT(G7,2)),1)>0,RIGHT(G7,1),IF(FIND(">",RIGHT(G7,3),1)>0,RIGHT(G7,2),RIGHT(G7,3)))

G7 contains a manually entered sequence of numbers which are entered to instruct a user referring to process document they can be as greater than 100, each number in the sequence is separated by the ">" symbol. I need to capture the final number in the sequence then concatenate this number to establish a unique reference number , some examples below;

1>2>3>5>6 (using the formula above this sequence works and captures a 6)

1>10>21>28 or 1>10>55>101 anything with 2 or more digits at the end of the sequence returns a #value!

Any workable suggestions would be much appreciated!? As I tried a number of other formulas with no success!

 

[XOR LX]

Hi,

Not quite sure I understood your description. You mean you simply want the number after the final ">"? If so:

=TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",255)),255))

or

=--TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",255)),255))

if you wish the returned value to be numeric (so that you can e.g. perform further mathematical operations upon it).

Regards

 

[oldbrewer]

[TABLE="width: 356"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1>10>21>28[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula in cell returning 1 is[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=FIND(">",RIGHT(C15,3))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]this "tells" you that[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]the rightmost 2 characters are not ">"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Roy Munson]

Hi,

Not quite sure I understood your description. You mean you simply want the number after the final ">"? If so:

=TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",255)),255))

or

=--TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",255)),255))

if you wish the returned value to be numeric (so that you can e.g. perform further mathematical operations upon it).

Regards

This is exactly what I was after thank you. Works a treat.

 

[XOR LX]

You're welcome.

 

[István Hirsch]

Also, you can give this formula a try:

=LOOKUP(99999,--RIGHT(G7,{1,2,3,4,5}))