We have a text cell that always say

"Some items ###.## australian dollars abcdefg"

So we always know there is "australian dollars" in the string. But numbers can be very small like .01 or very big like 1,000.33

How do we extract the numbers complete with decimal in simplest way?

3 Answers

It seems you are interested in just the "word" in front of "Australian dollars". We can extract the last word of a string using:

=TRIM(RIGHT(SUBSTITUTE(text," ",REPT(" ",100)),100))

So we just need to combine that with a LEFT function, because the "last" word we need would always sits in front of "Australian Dollars", according to your explaination. Combined with som TRIM it would come down to:

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,SEARCH("australian dollars",A1)-1))," ",REPT(" ",100)),100))

In an example of mixed type of strings we get:

Another option would be to look into power query. But when I just tried it, it does take a fair bit of steps so I think the quickest way would be using the formula.

You may try this when Number is followed by Text string:

• Formula in Cell B2, fill it down.

   =LEFT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9","0"},""))))

N.B. This method handles any length of characters after the Number.

Edited:

Following Formula can be used if Number is wrapped with Text string in Cell.

• Formula in Cell I45:

   =LOOKUP(9.9E+307,--LEFT(MID(H45,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $H45&"1023456789")),999),ROW(INDIRECT("1:999"))))

Where,

• LOOKUP(9.9E+307, find the largest value.
• INDIRECTcreates reference, never changes even when value in cells are inserted or deleted.

N.B. This method handles any length of characters after & before Number.

Adjust cell references in the Formula as needed.

if the phrase "australian dollars" does not change, you can use below formula

B2= Value(Left(A1,Len(A1)-19))

and format the B2 Cell as you like