Sum and subtract columns when one cell contains preceding text
 

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.

Sum and subtract columns when one cell contains preceding text
 

K Tuck wrote:
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+****+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

Sum and subtract columns when one cell contains preceding text
 

Hi,

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

Mike

"K Tuck" wrote:

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.

Sum and subtract columns when one cell contains preceding text
 

Yes.

"Mike H" wrote:

Hi,

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

Mike

"K Tuck" wrote:

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.

Sum and subtract columns when one cell contains preceding text
 

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.

"K Tuck" wrote:

Yes.

"Mike H" wrote:

Hi,

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

Mike

"K Tuck" wrote:

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.