Find the latest price and lowest price out of formatted text

In B2 this will return the latest price.

Formula:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),"Sold as a special offer ","")," ",REPT(" ",LEN(A2)),2),LEN(A2)))

The other is going to take me a while.

Are you open to helper columns?

Originally Posted by anonymous321

I don't understand "helper columns".

They are additional formulas which break down and simplify the final solution.

This proposed solution uses A LOT of helper columns.
The first one (D) is populated using:

Formula:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),"Sold as a special offer ","")," ",REPT(" ",LEN(A2)),1),LEN(A2)))

Column E is populated using:

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," Sold as a special offer",""),"Sold as a special offer ",""),D2,""),"$"," "))

Columns H:CL are populated using*:

Formula:

=IFERROR(FIXED(VALUE(MID($E2,SUM(LEN($G2:G2))+COLUMN(A:A),SEARCH(".",$E2,SUM(LEN($G2:G2))+COLUMN(A:A))+3-(SUM(LEN($G2:G2))+COLUMN(A:A)))),2),"")

Column C is populated using*:

Formula:

=D2&" "&IF(ISNUMBER(VALUE(MID(B2,LEN(D2)+2,1))),"",MID(B2,LEN(D2)+2,1))&FIXED(MIN(IFERROR(VALUE(H2:O2),FALSE)),2)

Note: Column F is populated with a formula which I hope can be incorporated into a user defined function (unfortunately I am VBA illiterate, but I'll ask some members from the VBA forum to take a look). If a UDF solution is possible then it should eliminate the need for a least the formula in columns H:CL.
Note: I am returning this as an .xlsx file because your profile indicates that you are using the 2007 version.
Let us know if you have any questions.

Your sample file says the latest price should be the first price from the right, but the examples are all the first from the left, so which is correct?

I have the same question as Don. I settled on left most figures. Additionally I get AU $55.30 for minimum price in A4. The anticipated is AU $59.95.

In the attached column D find this for the latest figure. It's the same one I posted in post #2 and does double duty as a helper in the min formula.

Formula:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),"Sold as a special offer ","")," ",REPT(" ",LEN(A2)),2),LEN(A2)))

Then for the minimums two helper formulas ... columns E:F ...

This cleans up and "standardizes" the strings.

Formula:

=SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),"$","")),"Sold as a special offer","")),LEFT(D2,FIND(" ",D2)),"")

This one locates the last "." in the standardized helpers.

Formula:

=LOOKUP(1E+306,FIND(".",E2,ROW(INDIRECT("1:"&LEN(E2)))))

Then array enter this in G2 and filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=LEFT(D2,FIND(" ",D2))&IF(ISNUMBER(FIND("$",A2)),"$","")&TEXT(MIN(--TRIM(RIGHT(SUBSTITUTE(LEFT(E2,FIND(".",E2,ROW(INDIRECT("1:"&F2)))+2)," ",REPT(" ",256)),256))),"#.00")

Try this, it's a start: