Extract Numbers from Text String

[Dastnai]

Hi Everyone, I am looking to extract numbers from text strings. I appreciate your help.

C&J ENERGY 1422213 NEAR $37.6 12/14/2018

PIONEER 1422215 THUMMJ $25 12/14/2018

<tbody>
</tbody>

$42/TN [Hold]
$37.60 [Hold]
C&J ENERGY 1422199 NEAR $84.80 12/14/2018

Desired outcome:
$37.6
$25
$42
$37.60
$84.80

Regards,

Dastnai

 

[PCL]

Assuming data in B2
Somewhere put =MID(B2,FIND("$",B2,1),FIND(" ",B2,FIND("$",B2,1))-FIND("$",B2,1))

 

[jtakw]

Hi,

Based on your samples, use one of the following.

B1 formula results as Text (extracts the $ value exactly as shown).
C1 formula Converts results to Real numbers for further math/comparison, Format result cells as Currency.

Either formula copied down.

Book1
	A	B	C
1	C&J ENERGY 1422213 NEAR $37.6 12/14/2018	$37.6	$37.60
2	PIONEER 1422215 THUMMJ $25 12/14/2018	$25	$25.00
3	$42/TN [Hold]	$42	$42.00
4	$37.60 [Hold]	$37.60	$37.60
5	C&J ENERGY 1422199 NEAR $84.80 12/14/2018	$84.80	$84.80
Sheet403

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30))
C1	=LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30)+0

 

[Dastnai]

Hi,

Based on your samples, use one of the following.

B1 formula results as Text (extracts the $ value exactly as shown).
C1 formula Converts results to Real numbers for further math/comparison, Format result cells as Currency.

Either formula copied down.

	A	B	C
1	C&J ENERGY 1422213 NEAR $37.6 12/14/2018	$37.6	$37.60
2	PIONEER 1422215 THUMMJ $25 12/14/2018	$25	$25.00
3	$42/TN [Hold]	$42	$42.00
4	$37.60 [Hold]	$37.60	$37.60
5	C&J ENERGY 1422199 NEAR $84.80 12/14/2018	$84.80	$84.80

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet403​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30)) C1 =LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30)+0 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Works perfectly. Thanks!

 

[jtakw]

You're welcome.