Extract number with decimal from a text string cell

[macjagger17]

Dear All,

Appreciate your assistance to my inquiry below. I wanted to extract the red highlighted decimal number (put in Column B) from the text string cell (Column A) as below.
I cannot do the convert text to column because of the spacing.
Anyone can assist on the formula to extract the highlighted decimal number?


Source - text string cell in Column A

SALES REPORT.xlsx
	A
1	TABLE AMOUNT VALUE VALUE
2	ID CREDITS DEBITS
3	ORIGINAL SALE 1,450 1,272,498.83CR 1,272,498.83
4	ORIGINAL SALE RVSL 1,121 122,218.74DR 122,218.74
5	ORIGINAL WITHDRAWAL 115 190,400.00CR 190,400.00
6	ORIGINAL WITHDRAWAL RVRSL A0186 5 9,000.00DB 9,000.00
7	TOTAL ATM CASH 120 181,400.00CR 190,400.00 9,000.00
8	NET ATM CASH 181,400.00
SALES

The decimal numbers I want in Column B

1,272,498.83

122,218.74

190,400.00

9,000.00

181,400.00

 

[QuestionBaker]

Not really proud of the solution, but I have a jugad way of solving it
It seems as if your data still has some sense of structure to it, assuming this to be the case, use text to columns but with fixed with and single out the numbers that you want...
Something like this

Excel should automatically convert those to numbers (if that doesn't happen, select the cells and press Ctrl + H and replace a <space> with nothing)
your final result will be something like this

So, I would recommend you to take a copy of your original data, and do this process, get the numbers, and then paste it in column B

If your data is not uniform, then this might not work

Please let me know

 

[jtakw]

Hi,

May be able come up with something a little simpler, but I'm making dinner, so this should do, result converted to Real Number:

Book3.xlsx
	A	B
1	TABLE AMOUNT VALUE VALUE
2	ID CREDITS DEBITS
3	ORIGINAL SALE 1,450 1,272,498.83CR 1,272,498.83	1,272,498.83
4	ORIGINAL SALE RVSL 1,121 122,218.74DR 122,218.74	122,218.74
5	ORIGINAL WITHDRAWAL 115 190,400.00CR 190,400.00	190,400.00
6	ORIGINAL WITHDRAWAL RVRSL A0186 5 9,000.00DB 9,000.00	9,000.00
7	TOTAL ATM CASH 120 181,400.00CR 190,400.00 9,000.00	181,400.00
8	NET ATM CASH 181,400.00
Sheet965

Cell Formulas
Range	Formula
B3:B7	B3	=RIGHT(SUBSTITUTE(LEFT(A3,1/LOOKUP(2,1/SEARCH({"CR ","DR ","DB "},A3))-1)," ",REPT(" ",99)),99)+0