Sum and subtract columns when one cell contains preceding text

  • Thread starter Thread starter K Tuck
  • Start date Start date
K

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.
 
K said:
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.
Click to expand...


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
 
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.
 
Back
Top