Problem with lookup formula - missing values

Hi, I have previously had some help extracting some numbers from a sentence, but have come across a few problems. The formula works well 50% of the time, but randomly skips over some values. I have attached a sample of what I'm working on.

Extracting example.xlsx

There will be thousands of questions similar, but think there will be a limit of 4 numbers to be taken out.

Thanks in advance

Georgette

In your SUBSTITUTE functions you need to ensure that you use the values found previously to 2 decimals given this is how they are listed in your string, eg:

B1 is 2.1 as a value but in your string it is "2.10" - you need to format therefore format 2.1 (b1) to 2 decimals such that the "2.10" is removed else removing 2.1 leaves "0" which is why it is the next found number... so revise per below:

Please Login or Register  to view this content.

You're not appalling, what you're doing is relatively complex - the reason you're now getting the results you are is that the quantity is being found first ... I have to say I saw your first thread on this and NBVC's approach utilising Regular Expressions (c/o Laurent Longre's morefunc.xll) is I think best suited to this type of task (my opinion), eg:

B1: =REGEX.MID(A1,"(£[0-9]+\.[0-9]{2})")
copied down

You can still do what you're doing using native functions but they will become pretty complicated ... problem with Reg Exp is that they're not exactly intuitive.

No problem.

FYI: in terms of the original approach - it becomes something like:

Please Login or Register  to view this content.

etc but as you can see things will get increasingly messy...

Originally Posted by Geomarsh

Think I've had a breakthrough in my understanding of this all. I only have to do the TEXT function for values that will be in the format ??/?? or similar. Interger values i can use the original SUBSTITUTE function!!

Yes, in reality there are no numbers per se in A1 given it is in fact a text string ... think of it like:

=SUBSTITUTE("I have apples","apple","") --> "I have s"

=SUBSTITUTE("I have £2.10","2.1","") --> "I have £0"

ie "£2.10" is treated no differently to "apples" given they are both text strings in this context, "2.1" <> "2.10" as "apple" <> "apples".