Extracting currency amounts from a string of numbers and text

[MichaelE7]

Hi there,
I'm looking for a formula to extract a currency amount from a string of numbers and text.

Eg: [TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl77, width: 624"]TextIsHereWithSomeNumber3.5m - $445,544.21

I am only looking for the dollar amount.

Is it possible to extract this info with a formula or will I have to do this manually?

thanks[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

You only showed One sample, which may Not be representative of your actual data, this works if All your data is in the Same format as your sample, where the Currency is at the End of the text string:

Book1
	A	B	C	D
1	TextIsHereWithSomeNumber3.5m - $445,544.21		$445,544.21	$445,544.21
2
3			^ Format as
4			Currency
Sheet365

Cell Formulas
Range	Formula
C1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30)+0
D1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))

C1 formula converts results to Real Numbers, format cell as currency if desired.
D1 formula Only extracts the currency value from the original Text String, and remains as Text.

 

[jtakw]

Alternate formulas included:

Book1
	A	B	C	D	E	F	G
1	TextIsHereWithSomeNumber3.5m - $445,544.21		$445,544.21	$445,544.21		$445,544.21	$445,544.21
2
3			^ Format as			^ Format as
4			Currency			Currency
Sheet365

Cell Formulas
Range	Formula
C1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30)+0
D1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))
F1	=MID(A1,FIND("- ",A1)+2,30)+0
G1	=MID(A1,FIND("- ",A1)+2,30)

C1 and F1 formula converts results to Real Numbers, format cell as currency if desired.
D1 and G1 formula Only extracts the currency value from the original Text String, and remains as Text.

F1 and G1 formula assumes there's Always a hyphen "-" before the currency value and there's No other hyphens within the original Text string.

 

[jtakw]

Got your PM, please continue questions relevant to your post within the thread, that way you'll get more responses from Other possible helpers.

For the strings you provided in the PM, C1 and D1 formulas worked for me, if it doesn't work for you, please provide the incorrect results and/or Errors:

Book1
	A	B	C	D
1	TextIsHereWithSomeNumber3.5m - $445,544.21		$445,544.21	$445,544.21
2	XERION 5000 TRAC - Tier 3a - R 1,123,110.05		$1,123,110.05	1,123,110.05
3	JAGUAR 960 - Tier 3 - R 1,568,136.00		$1,568,136.00	1,568,136.00
4	BRUTTUS 6000 4M with Topper 4500 DGPS Variable Rate SHS (Independent Hyd Sys) with Transport Wheels - R 123,150.21		$123,150.21	123,150.21
5
6			^ Format as
7			Currency
Sheet365

Cell Formulas
Range	Formula
C1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30)+0
D1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))