Extract Dollar Amounts From Cells that Contain Additional Data and Vary in Position for Google Sheets

[ClimaPod]

Hello! I'm trying to extract dollar amounts from a cell that contains additional data and the dollar amounts can vary in position. The dollar amounts are all formatted with a $ but are not all the same length after. For example, if you look at the below screenshot, you'll see column R is labeled 'Notes / Ship Rates / Tracking' and column S is where I'm trying to extract the 'Ship Rates' to.

I found a similar situation through a different post which provided the formula you can see being used in the screenshot:

=MID(R2,FIND("$",R2),FIND(" ",R2&" ",FIND("$",R2))-FIND("$",R2))

As you can see, in Column S the formula is extracting the shipping rate, but it's also pulling over the first word (Tracking) in the next line down. I've tried adjusting the formula but can't seem to figure out how to get it to pull only the Shipping rate without any additional information. Any thoughts or suggestions?

Here is the link to the post I was referencing in case it may be helpful.

Extract from cell dollar amounts that vary in position and length

I'm trying to extract dollar amounts from a cell. The dollar amounts are all formatted with a $ but are not all the same length after the $, e.g. $100, $1235, $25, etc. Also, the dollar amounts appear in different positions in the various text strings, e.g. $100 gift certificate, Spa package for...

www.mrexcel.com

 

[Fluff]

Hi & welcome to MrExcel.
Is this for Excel or Google Sheets?

 

[alansidman]

Without seeing your actual file, this is only a guess. I am guessing that there is no space after the price just a line feed. Therefore the next space is after "Tracking."

 

[Sufiyan97]

With data as shown in example

Try

Extract Dollar Amounts From Cells that Contain Additional Data and Vary in Position.xlsx
	A	B	C
1	Data	Text	Number
2	order # #7674 FQ Estes $447.88 Tracking # 12345	$447.88	447.88
3	PROMISED SHIP IN JULY FQ XPO $418.86 Tracking # 12345	$418.86	418.86
4	UPS $22.56 Tracking 12345 Paid 7/21/2021	$22.56	22.56
5	FQ Estes $448.95 Tracking # 12345 Paid 8/5/2021	$448.95	448.95
6	order # 7678 GF ABF $523.17 Tracking # 12345 Paid 8/4/2021	$523.17	523.17
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),"@",2),CHAR(10),"@",1),CHAR(10),"@",3),FIND("$",SUBSTITUTE(A2,CHAR(10),"@",2)),99),"@",REPT(" ",99)),99))
C2:C6	C2	=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),"@",2),CHAR(10),"@",1),CHAR(10),"@",3),FIND("$",SUBSTITUTE(A2,CHAR(10),"@",2)),99),"@",REPT(" ",99)),99))+0

if you want dollar amounts as numbers try formula in column C

 

[ClimaPod]

Hi & welcome to MrExcel.
Is this for Excel or Google Sheets?

Hello, this is for Google sheets. I believe the formula that Sufiyan97 shared is going to do the trick for me though. I need to leave the office for the day so I will have to investigate this further tomorrow and then follow up.

 

[alansidman]

@Fluff
What was the clue that made you ask if this was a Google Sheets issue? I didn't see anything.
Alan

 

[Fluff]

this is for Google sheets

Thanks for that. In future if you want a formula for anything other than Excel, you need to state that & post in the General Discussion & Other Applications section.
I have moved it for you this time.

@alansidman it's the fx symbol that tip me off, as it's significantly different from the Excel version.

 

[Fluff]

Another option that should work with sheets

+Fluff 1.xlsm
	A	B
1	Data
2	order # #7674 FQ Estes $447.88 Tracking # 12345	447.88
3	PROMISED SHIP IN JULY FQ XPO $418.86 Tracking # 12345	418.86
4	UPS $22.56 Tracking 12345 Paid 7/21/2021	22.56
5	FQ Estes $448.95 Tracking # 12345 Paid 8/5/2021	448.95
6	order # 7678 GF ABF $523.17 Tracking # 12345 Paid 8/4/2021	523.17
Master

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(REPLACE(A2,1,FIND("$",A2),""),FIND(" ",SUBSTITUTE(REPLACE(A2,1,FIND("$",A2),""),CHAR(10)," "))-1)

 

[alansidman]

@Fluff. We should change your moniker to "Eagle Eye" I had to look at it several times after you posted to note the difference.