Trim text in string

[sprigelz]

I have a text string that looks like this, Accu-Chek Aviva Plus $30 Retail Qty. 50

I am able to get the text before the $ sign very easily. I also need to be able to extract the price, and text after the price in separate cells.

Final product should look like this;

Accu-Chek Aviva Plus

$30

Retail Qty. 50

The main issue I am having is that the price, including the $ sign, can be between 2-4 characters long. I attempted to use =(mid(A8,find("$",A8),4)) but unfortunately that still pulled text after the price in some cases. Any help would be greatly appreciated!

 

[CA_Punit]

This might help

Book1
	A	B	C	D	E	F	G	H
1	Accu-Chek Aviva Plus $30 Retail Qty. 50					Accu-Chek Aviva Plus	$30	Retail Qty. 50
Sheet1

Cell Formulas
Range	Formula
F1:H1	F1	=CHOOSE({1,2,3},LEFT(A1,SEARCH("$",A1)-1),MID(MID(A1,SEARCH("$",A1),500),1,SEARCH(" ",MID(A1,SEARCH("$",A1),500))-1),MID(MID(A1,SEARCH("$",A1),500),SEARCH(" ",MID(A1,SEARCH("$",A1),500)),500))
Dynamic array formulas.

 

[sandy666]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Before = Table.AddColumn(Source, "Before", each Text.BeforeDelimiter([raw], " $"), type text),
    Between = Table.AddColumn(Before, "Between", each Text.BetweenDelimiters([raw], " ", " ", {3, RelativePosition.FromEnd}, 0), type text),
    After = Table.AddColumn(Between, "After", each Text.AfterDelimiter([raw], "0"), type text),
    RC = Table.RemoveColumns(After,{"raw"})
in
    RC

raw		Before	Between	After
Accu-Chek Aviva Plus $30 Retail Qty. 50		Accu-Chek Aviva Plus	$30	Retail Qty. 50

 

[Peter_SSs]

I would use 3 separate (shorter) formulas.

20 11 05.xlsm
	A	B	C	D
1	Accu-Chek Aviva Plus $30 Retail Qty. 50	Accu-Chek Aviva Plus	$30	Retail Qty. 50
Extract Text

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(A1,FIND("$",A1)-1))
C1	C1	=TRIM(SUBSTITUTE(LEFT(A1,FIND(" ",A1,LEN(B1)+2)),B1,""))
D1	D1	=REPLACE(A1,1,FIND(" ",A1,FIND(C1,A1)),"")

 

[sprigelz]

@Peter_SSs Thank you. This worked perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.