# Extract number with decimal from a text string cell

#### macjagger17

##### New Member
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

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
AB
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
RangeFormula
B3:B7B3=RIGHT(SUBSTITUTE(LEFT(A3,1/LOOKUP(2,1/SEARCH({"CR ","DR ","DB "},A3))-1)," ",REPT(" ",99)),99)+0

Replies
13
Views
640
Replies
19
Views
506
Replies
4
Views
531
Replies
3
Views
94
Replies
9
Views
247

1,203,762
Messages
6,057,218
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back