Pull a Value from a Cell containing a String?

Posted by Stan on January 24, 2002 8:05 PM

Can anyone help?
I want to know if I can pull a Value from a Cell containing a String. Example cells containing (Tom1), Stan2), (Mark3) etc. Each name has a seniority number with it. In my sheet I need to sort by seniority number. Any help would be great! Thanks

Posted by Bariloche on January 24, 2002 8:41 PM

Stan,

Assuming that your text is in A1 and is of the form (Name#) then this formula will put the # in another cell:

=VALUE(RIGHT(LEFT(A1,LEN(A1)-1),1))

If your text can be of the form (Name##) then this formula needs to be used:

=IF(ISNUMBER(VALUE(RIGHT(LEFT(A6,LEN(A6)-2),1))),VALUE(VALUE(RIGHT(LEFT(A6,LEN(A6)-2),1))&VALUE(RIGHT(LEFT(A6,LEN(A6)-1),1))),VALUE(RIGHT(LEFT(A6,LEN(A6)-1),1)))

Hopefully your seniority numbers don't get to three digits.

There may be (hopefully) a more elegant solution, but this will get you started.

enjoy