Extract Number from Cell with decimals

[DIPASGL]

Hi
Please help me in this.

I am having below numbers

AUD 76,946.71 CR

AUD 92,421.49 CR

and I want to get below result.

76,946.71

2,92,421.493

I used below but its giving me number without decimal which I do not want.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

 

[Fluff]

How about

=--TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",100)),100,100))

 

[RudRud]

Book1
	A	B	C
1	AUD 76,946.71 CR		76,946.71
2	AUD 92,421.49 CR		92,421.49
3	AUD 92,421.492 CR		92,421.492
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=IFERROR(MID(A1:A3,1+LEN(TEXTBEFORE(A1:A3,SEQUENCE(10,,0))),10),"Not Found")
Dynamic array formulas.

=IFERROR(MID(A1:A3,1+LEN(TEXTBEFORE(A1:A3,SEQUENCE(10,,0))),10),"Not Found")*1 - for numeric

This single formula will return (spill) all of your results (just change the two instances of A9:A11 to the range you want to process)...

=IFERROR(MID(A9:A11,1+LEN(TEXTBEFORE(A9:A11,SEQUENCE(10,,0))),6),"Not Found")

Formula from @Rick Rothstein

 

[Fluff]

@RudRud The OP clearly shows they are using xl2003 or older & therefore won't have most of those functions.

 

[pjmorris]

how about this (Fluff's is ingenious, this is a bit more brute strength - really not sure which would be better though Fluff's is perhaps easier to read):

=--(LEFT(RIGHT(A1, LEN(A1)-FIND(" ",A1)),FIND(" ", RIGHT(A1, LEN(A1)-FIND(" ",A1)))))

 

[DIPASGL]

Thanks so much pjmorris, Fluff and RudRud for help.

I am using excel online browser for work for my files. GREAT HELP guys.

 

[Fluff]

In that case you could use

=INDEX(TEXTSPLIT(B5," "),,2)

I would also suggest that you mention using xl online as it has a lot more functions than 2003.

 

[DIPASGL]

In that case you could use

=INDEX(TEXTSPLIT(B5," "),,2)

I would also suggest that you mention using xl online as it has a lot more functions than 2003.

I will check. Thanks Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Limited sample data but taking a guess that the data all has a currency prefix (3 letters) & a CR or DR suffix (2 letters) then for all versions you could possibly use this

23 06 26.xlsm
	B	C
5	AUD 76,946.71 CR	76946.71
6	AUD 92,421.49 CR	92421.49
7	USD 362.00 DR	362
Extract Number

Cell Formulas
Range	Formula
C5:C7	C5	=--MID(LEFT(B5,LEN(B5)-2),4,20)

Also, if sticking with the TEXTSPLIT option and want numerical results not text

=--INDEX(TEXTSPLIT(B5," "),,2)