Extract text from a cell with multiple "-"

[gromden]

I have a product name that contains the name and the size. I only want the product name and used the following to extract the text I want out.

Example:
=TRIM(LEFT(Z1,(FIND("-",Z1,1)-1)))
"Leopard Print Jumpsuit (Heather Grey) - Large" and it correctly comes out as "Leopard Print Jumpsuit (Heather Grey)"

However, the issue I am coming across is that some products have another "-" in the description such as "Dusty Teal 3-4 Sleeve Kimono with Lace Trim - M" returns "Dusty Teal 3"

I use to have success using =LEFT(Z1,(FIND(")",Z1))) as an alternative formula but it returns #value.

Anyone have another option I could try?

Thanks!

 

[jtakw]

Hi,

This will work no matter how many "-" (hyphen) you have in the text string:

Book3.xlsx
	A	B
1	Leopard Print Jumpsuit (Heather Grey) - Large	Leopard Print Jumpsuit (Heather Grey)
2	Dusty Teal 3-4 Sleeve Kimono with Lace Trim - M	Dusty Teal 3-4 Sleeve Kimono with Lace Trim
3	Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress - S	Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress
4	Leopard - Print-Jumpsuit - (Heather - Grey) - Large	Leopard - Print-Jumpsuit - (Heather - Grey)
Sheet1098

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(LEFT(A1,FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1))

 

[gromden]

thank you!

 

[jtakw]

You're welcome.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Anyone have another option I could try?

Yes, depending on your version information requested above and also if you happen to be one of the lucky ones who has received the latest batch of new functions. If you have a relevant subscription version but not the TEXTBEFORE function then you should get the function sometime soon(ish).

22 04 15.xlsm
	A	B
1	Leopard Print Jumpsuit (Heather Grey) - Large	Leopard Print Jumpsuit (Heather Grey)
2	Dusty Teal 3-4 Sleeve Kimono with Lace Trim - M	Dusty Teal 3-4 Sleeve Kimono with Lace Trim
3	Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress - S	Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress
4	Leopard - Print-Jumpsuit - (Heather - Grey) - Large	Leopard - Print-Jumpsuit - (Heather - Grey)
Product Name

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(TEXTBEFORE(A1,"-",-1))