Subtract Text-Based Hours Except D Codes in Excel
Question details
Subtract hours contained in text codes from scheduled hours while excluding codes that begin with D.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A table row contains scheduled hours and several coded entries, such as A8 and D8.
- Observed behavior
- The desired result is scheduled hours minus the numeric portions of eligible codes, with D-prefixed codes excluded.
Check that your data is in an Excel table and identify the scheduled-hours column and the first and last code columns. The formula below assumes eligible codes have one leading character followed by a number, such as A8.
Use a structured-reference formula in the Excel table
Subtract the numeric portions of coded entries while ignoring codes that start with D.
The formula removes the first character of each eligible code, converts the remaining text to a number, and treats invalid entries as zero. It does not count standalone numbers because it removes their first character.
Click a cell in a new result column on the same table row as the scheduled hours and codes.
Enter =[@[Scheduled Hours]]-SUM(IFERROR(--IF(LEFT(Table27[@[M1]:[F2]])<>"D",MID(Table27[@[M1]:[F2]],2,99)),0)). Replace Table27, Scheduled Hours, M1, and F2 with your actual table and column names.
Press Enter, then check a row containing both an eligible code such as A8 and an excluded code such as D8. Excel should subtract 8 for A8 and nothing for D8.

Subtract eligible coded hours with WPS Spreadsheets
Use helper cells in WPS Spreadsheets to extract hours from codes, exclude D-prefixed entries, and subtract the total from scheduled hours.
- 1. Open the workbook: Open your workbook in WPS Spreadsheets. For this example, place scheduled hours in A2 and codes in B2:G2.
- 2. Extract eligible hours: In H2, enter =IF(LEFT(B2,1)="D",0,IFERROR(VALUE(B2),IFERROR(VALUE(MID(B2,2,99)),0))). This counts standalone numbers and numbers after a one-character prefix, but ignores D-prefixed codes.
- 3. Fill across the helper cells: Select H2 and drag its fill handle across to M2 so each helper cell evaluates the corresponding code in B2:G2.
- 4. Subtract the total: In N2, enter =A2-SUM(H2:M2). To calculate additional rows, copy H2:N2 down alongside your data.

Frequently Asked Questions
Will the Excel formula ignore both D8 and d8?
Yes. Excel text comparisons are generally case-insensitive, so LEFT compared with "D" excludes both D8 and d8.
What happens if a code has no valid number after its first character?
The IFERROR part of the formula treats the entry as zero, so it does not change the scheduled-hours total.
Can the Excel table formula count a standalone number such as 8?
Not as written: MID removes the first character, leaving nothing to convert. Use the WPS helper-cell formula shown above if your cells also contain standalone numbers.
What do M1 and F2 mean in the structured reference?
They are the first and last column headers in the table range, not worksheet cell addresses. Replace them with the headers that bound your adjacent code columns.




