Extract number with decimal from a text string cell

[macjagger17]

Dear All,

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


Source - text string cell in Column A

1/11/21 960 C P GL 71,959.26 71,959.26 CR

24/01/22 10 D A CC 10.90 348.46 CR

2/12/21 60 C A CC 70,076.00 320.50 DR

24/01/22 10 D A CC 417.50 1,445.46 DR

1/11/21 960 C P GL 71,959.26 1,143,461.42 CR

The decimal numbers I want in Column B

71,959.26​

348.46​

320.50​

1,445.46​

1,143,461.42​

 

[arthurbr]

Try text to columns - use space as delimiter and in the last windows select all columns you do not want to import

 

[macjagger17]

Try text to columns - use space as delimiter and in the last windows select all columns you do not want to import

OMG. thank you so much, arthurbr!!! really saved a lot of time than wasting my time to copy out and paste the number! btw is there any kind of formula to do like text to columns?

​

 

[arthurbr]

With a non-US version one possibility

=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";50);6);20));{"CR"\"DR"};" "))+0

For US versions replace the semi-colon with a comma and the backslash also with a comma ( I think)
There should be shorter versions, though

 

[macjagger17]

With a non-US version one possibility

=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";50);6);20));{"CR"\"DR"};" "))+0

For US versions replace the semi-colon with a comma and the backslash also with a comma ( I think)
There should be shorter versions, though

Thank you so muchhh, arthurbr! really appreciates it!

 

[jtakw]

Hi,

Just wanted to point out SUBSTITUTE can't handle arrays in that way, they must be done separately, here's a corrected version plus my suggestion:

Book3.xlsx
	A	B	C	D
1		jtakw	Post #4 corrected	From Post #4
2	1/11/21 960 C P GL 71,959.26 71,959.26 CR	71959.26	71959.26	71959.26
3	24/01/22 10 D A CC 10.90 348.46 CR	348.46	348.46	348.46
4	2/12/21 60 C A CC 70,076.00 320.50 DR	320.5	320.5	#VALUE!
5	24/01/22 10 D A CC 417.50 1,445.46 DR	1445.46	1445.46	#VALUE!
6	1/11/21 960 C P GL 71,959.26 1,143,461.42 CR	1143461.42	1143461.42	1143461.42
Sheet944

Cell Formulas
Range	Formula
B2:B6	B2	=MID(SUBSTITUTE(A2," ",REPT(" ",100)),600,100)+0
C2:C6	C2	=SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50),6),20),"CR",""),"DR","")+0
D2:D6	D2	=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50),6),20)),{"CR","DR"}," "))+0

 

[macjagger17]

Hi,

Just wanted to point out SUBSTITUTE can't handle arrays in that way, they must be done separately, here's a corrected version plus my suggestion:

Book3.xlsx
	A	B	C	D
1		jtakw	Post #4 corrected	From Post #4
2	1/11/21 960 C P GL 71,959.26 71,959.26 CR	71959.26	71959.26	71959.26
3	24/01/22 10 D A CC 10.90 348.46 CR	348.46	348.46	348.46
4	2/12/21 60 C A CC 70,076.00 320.50 DR	320.5	320.5	#VALUE!
5	24/01/22 10 D A CC 417.50 1,445.46 DR	1445.46	1445.46	#VALUE!
6	1/11/21 960 C P GL 71,959.26 1,143,461.42 CR	1143461.42	1143461.42	1143461.42
Sheet944

Cell Formulas
Range	Formula
B2:B6	B2	=MID(SUBSTITUTE(A2," ",REPT(" ",100)),600,100)+0
C2:C6	C2	=SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50),6),20),"CR",""),"DR","")+0
D2:D6	D2	=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50),6),20)),{"CR","DR"}," "))+0

Hi jtakw, thank you so much for your help! Really works great! I can use your formula in Column B & C for my excel!

 

[jtakw]

You're welcome.