# Formula to establish the sequence number within a string?

#### Roy Munson

##### New Member
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!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### XOR LX

##### Well-known Member
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

##### Board Regular
 1>10>21>28 1 formula in cell returning 1 is =FIND(">",RIGHT(C15,3)) this "tells" you that the rightmost 2 characters are not ">"

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

#### Roy Munson

##### New Member
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.

You're welcome.

#### István Hirsch

##### Well-known Member
Also, you can give this formula a try:

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

Replies
3
Views
52
Replies
8
Views
109
Replies
1
Views
34
Replies
6
Views
81
Replies
1
Views
31