Stripping the alpha character from the left side of string...

[Lidsavr]

I am running Excel 2007.

I have two values that contain 1 or 2 alpha characters and 1 to 5 numbers.

Example:
Cell J3 contains RN9999
Cell J4 contains RN10005
Cell J5 will contain the formula to subtract J4-J3. I then want to subtract a value of 1 from the answer.

I have tried:

=(J4-J3)-1

which returns a '#VALUE' error

Additionally, I've tried:

=(MAX(J3:J4)-MIN(J3:J4))-1

this returns an answer -1.

I think my best bet is to strip off the alpha characters but sometimes there is one alpha character and sometimes two. I am not sure how to account for this variable.

Can anyone help me with this?

Thank you,

 

[Dryver14]

=RIGHT(J4,LEN(J4)-2)-RIGHT(J3,LEN(J3)-2)-1

will return 5

 

[Haseeb Avarakkan]

Hello, Try,

=LOOKUP(1E+307,CHOOSE({1,2},0,LOOKUP(1E+307,--RIGHT(J4,{1,2,3,4,5,6,7,8,9}))-LOOKUP(1E+307,--RIGHT(J3,{1,2,3,4,5,6,7,8,9}))-1))

 

[Aladin Akyurek]

I am running Excel 2007.

I have two values that contain 1 or 2 alpha characters and 1 to 5 numbers.

Example:
Cell J3 contains RN9999
Cell J4 contains RN10005
Cell J5 will contain the formula to subtract J4-J3. I then want to subtract a value of 1 from the answer.

I have tried:

=(J4-J3)-1

which returns a '#VALUE' error

Additionally, I've tried:

=(MAX(J3:J4)-MIN(J3:J4))-1

this returns an answer -1.

I think my best bet is to strip off the alpha characters but sometimes there is one alpha character and sometimes two. I am not sure how to account for this variable.

Can anyone help me with this?

Thank you,

Put up in a general form...

J5:

=1+(REPLACE(J4,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J4&"0123456789"))-1,"")-
    REPLACE(J3,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J3&"0123456789"))-1,""))

 

[Lidsavr]

Dryver14,
Many thanks! I double-checked to ensure the formula works with 1 or 2 alpha characters and it does! I would like to understand the formula more, but for now, I can get my work done. You are a huge help!

 

[Dryver14]

Mine shouldn't work with 1 alpha characters I dont think asit excludes the first 2 items from the number For that the other formulas posted are much better I think as they are seeking out alpha numeric characters

 

[Just Jan]

Another solution, relies on there being only 1 or 2 letter prefixes
A1 is code
B1 =IF(ISNUMBER(VALUE(MID(A1,2,1))),VALUE(MID(A1,2,LEN(A1))),VALUE(MID(A1,3,LEN(A1))))
then do the maths using col B values