Extract Numbers from Text String

[Dastnai]

Hi Everyone, I am looking to extract numbers from text strings. I appreciate your help. [TABLE="width: 372"]
<tbody>[TR]
[TD]



C&J ENERGY 1422213 NEAR $37.6 12/14/2018
[/TD]
[/TR]
[TR]
[TD]PIONEER 1422215 THUMMJ $25 12/14/2018
[/TD]
[/TR]
</tbody>[/TABLE]
$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
C&J ENERGY 1422213 NEAR $37.6 12/14/2018
PIONEER 1422215 THUMMJ $25 12/14/2018
$42/TN [Hold]
$37.60 [Hold]
C&J ENERGY 1422199 NEAR $84.80 12/14/2018

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]$37.6[/TD]
[TD="align: right"]$37.60[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$25[/TD]
[TD="align: right"]$25.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$42[/TD]
[TD="align: right"]$42.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$37.60[/TD]
[TD="align: right"]$37.60[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$84.80[/TD]
[TD="align: right"]$84.80[/TD]

</tbody>

Sheet403​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("$",A1),30),"/"," ")," ",REPT(" ",30)),30)+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Works perfectly. Thanks!

 

[jtakw]

You're welcome.