Sum and subtract columns when one cell contains preceding text

[K Tuck]

I need to add columns: A1=JA1098027 and B1=6, then subtract C1=1. All of
column A contains 1-3 letters preceding the number so nothing I've tried
works. Columns B and C are numeric only. Anyone know the secret formula for
this one? Thanks.

 

[Glenn]

I need to add columns: A1=JA1098027 and B1=6, then subtract C1=1. All of
column A contains 1-3 letters preceding the number so nothing I've tried
works. Columns B and C are numeric only. Anyone know the secret formula for
this one? Thanks.

This array formula (commit with CTRL+SHIT+ENTER) should give you the correct answer:

=MID(A1,MATCH(1,--NOT(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)),0),LEN(A1))+B1-C1

 

[Mike H]

Hi,

Do you want to keep the leading letters in the end result?

Mike

 

[K Tuck]

Yes.

Hi,

Do you want to keep the leading letters in the end result?

Mike

 

[K Tuck]

By the way, Mike H's suggested formula did not work. It results in #N/A so
anything you can suggest is very much appreciated. Thanks.