Formula to establish the sequence number within a string?

Roy Munson

New Member
Joined
Sep 23, 2011
Messages
2
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!
 

Some videos you may like

Excel Facts

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

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Apr 11, 2010
Messages
11,003
1>10>21>281
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
Joined
Sep 23, 2011
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,730
Messages
5,524,491
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top