Challenge Scenario
When importing financial transaction reports from legacy banking software or external payment gateways, transaction codes often arrive corrupted with prefix and suffix letters (such as "TX9042A" or "REF1820X").
To prepare these codes for database insertion and mathematical lookup, data engineers extract only the core numeric digits by slicing the text between known character positions.
As a Financial Data Quality Specialist, your tasks are:
- Clean Digits (Column C): Extract the 4 core numeric digits from raw strings starting at character position 3 using
MID(B2, 3, 4).
- Converted Value (Column D): Convert the extracted text string into an authentic Excel number using
VALUE(C2) or =--C2.
- Total Transactions (Cell B8): Count total transaction records using
COUNTA(A2:A5).
- Sum of Digit Values (Cell C8): Calculate the sum of all converted numeric values using
SUM(D2:D5).
Your Sheet Layout
Understanding how the transaction sanitation worksheet is structured:
- Raw Transaction Strings (Columns A & B): Records customer names and raw alpha-numeric transaction strings (
TX9042A, TX1820B, etc.).
- Digit Extraction & Type Conversion (Columns C & D): Slices the 4-digit code with
MID and casts it to a numeric data type with VALUE.
- Data Audit Summary (Rows 7 & 8): Summarizes total records processed and calculates the mathematical sum of the sanitized numbers.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Customer, Raw String |
Raw Data |
Customer names and corrupted transaction codes |
Read-only incoming raw payment strings. |
C2:C5 |
Clean Digits |
Text Extraction |
=MID(B2, 3, 4) |
Extracts the 4-digit substring starting from character 3. |
D2:D5 |
Numeric Value |
Type Conversion |
=VALUE(C2) |
Converts extracted text digits into a real number. |
B8 |
Total Records |
Summary |
=COUNTA(A2:A5) |
Counts total customer transaction rows (4). |
C8 |
Sum of Values |
Summary |
=SUM(D2:D5) |
Calculates the mathematical sum of all cleaned numbers (24,195). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column B (B2:B5), strip non-numeric text and convert to numbers using SUBSTITUTE and VALUE.
2
In cell B8, calculate Total Inventory Value using SUM(B2:B5).
3
In cell C8, calculate Average Price using AVERAGE(B2:B5).
4
In cell D8, find the highest catalog price using MAX(B2:B5).
Solution & Formula Breakdown
Let's examine how text extraction and numerical conversion work together:
1. Slicing Substrings with MID (C2:C5)
=MID(B2, 3, 4)
The MID(text, start_num, num_chars) function extracts characters from the middle of a string:
3: Skips the 2-letter prefix ("TX") and starts at the 3rd character.4: Grabs exactly 4 characters.- Row 2 (
"TX9042A"): Extracts "9042".
- Row 3 (
"TX1820B"): Extracts "1820".
- Row 4 (
"TX7351C"): Extracts "7351".
- Row 5 (
"TX5982D"): Extracts "5982".
2. Converting Text to Real Numbers with VALUE (D2:D5)
=VALUE(C2)
Because MID outputs a text string ("9042"), functions like SUM cannot add it directly. VALUE("9042") converts the text into the authentic integer number 9042.
3. Summary Rollups (B8 & C8)
- Total Records (Cell B8):
=COUNTA(A2:A5) returns 4.
- Sum of Digit Values (Cell C8):
=SUM(D2:D5) adds 9042 + 1820 + 7351 + 5982, giving 24,195.
In spreadsheets, text numbers align to the left of the cell, while real numbers align to the right. Using VALUE converts left-aligned text strings into right-aligned calculable numbers!