Function Problems

Subtract Text-Based Hours Except D Codes in Excel

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Subtract hours contained in text codes from scheduled hours while excluding codes that begin with D.

Subtract Text-Based Hours Except D Codes in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click a cell in a new result column on the same table row as the scheduled hours and codes.

2
Enter the formula

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.

3
Confirm and check the result

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.

Use a structured-reference formula in the Excel table
Older Excel versions: If the formula does not calculate as expected in an older version of Excel, select the formula cell, press F2, and confirm it with Ctrl+Shift+Enter.
Calculate coded hours in WPS Spreadsheets

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. 1. Open the workbook: Open your workbook in WPS Spreadsheets. For this example, place scheduled hours in A2 and codes in B2:G2.
  2. 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. 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. 4. Subtract the total: In N2, enter =A2-SUM(H2:M2). To calculate additional rows, copy H2:N2 down alongside your data.
Exclude D-prefixed codes such as D8 from hour totalsCount standalone numbers as well as codes such as A8Open and save Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and cell references
microsoft office alternative - wps office

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.