| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting dependent of preceding cell | Excel Discussion (Misc queries) | |||
| fill cell with colour if preceding cell is a negative value | Excel Discussion (Misc queries) | |||
| How do I reference a cell to another in the preceding worksheet | Excel Worksheet Functions | |||
| Subtract text in one cell from another? | Excel Worksheet Functions | |||
| Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
Linear Mode
