Hmmm..."pathological"
Interesting choice of word, Dick

Per Merriam-Webster:
"being such to a degree that is extreme, excessive, or markedly abnormal"

Exactly what I was trying to demonstrate....that perhaps the structure of
the text wasn't as rigid as we might have thought, ultimately requiring a
much more arcane solution....as you so aptly provided.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

Ron Rosenfeld wrote...
....
This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))

This fails with Ron Coderre's pathological samples,

These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

IMO, you need to use REGEX.SUBSTITUTE to do this.

REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]")