Pull the amount out of a statement line (MID + VALUE)
The problem
Problem statement
Bank exports arrive as one string: date, reference and amount separated by pipes, as in A2. Put the amount for the line in A2 in the Amount cell B2, as a real number.
The amount is whatever follows the second pipe — it must be parsed, not typed, and it must end up numeric.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | |
|---|---|---|
| 1 | Statement line | Amount |
| 2 | 2026-02-03 | INV-7781 | 1250.00 | |
| 3 | 2026-03-01 | INV-9002 | 480.50 |
Notes
FIND gives the position of the second pipe, MID takes everything after it, TRIM removes the padding, and VALUE turns the text into a number so it can be summed. Splitting on a fixed position instead of the delimiter breaks the first time a reference is longer.
How to approach
Your answer goes in B2.
- Target cell is B2
- Find the second pipe: FIND("|", A2, FIND("|", A2)+1)
- Take the text after it
- Shape: =VALUE(TRIM(MID(?, ?, ?)))
Function guide
FAQ
How do I split a delimited string in Excel?
FIND the delimiter, then MID or RIGHT to take the part you want. Text to Columns or TEXTSPLIT do it in bulk, but a formula keeps it live.
Why does VALUE fail on my parsed text?
The extracted text still has spaces or a thousands separator. TRIM it, and SUBSTITUTE away commas before VALUE.