Extract text from a cell with multiple "-"

gromden

New Member
Joined
Jun 18, 2015
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,

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

Book3.xlsx
AB
1Leopard Print Jumpsuit (Heather Grey) - LargeLeopard Print Jumpsuit (Heather Grey)
2Dusty Teal 3-4 Sleeve Kimono with Lace Trim - MDusty Teal 3-4 Sleeve Kimono with Lace Trim
3Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress - SLilac Floral Smocked 3-4 Ruffle Sleeve-Dress
4Leopard - Print-Jumpsuit - (Heather - Grey) - LargeLeopard - Print-Jumpsuit - (Heather - Grey)
Sheet1098
Cell Formulas
RangeFormula
B1:B4B1=TRIM(LEFT(A1,FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1))
 
Upvote 0
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
AB
1Leopard Print Jumpsuit (Heather Grey) - LargeLeopard Print Jumpsuit (Heather Grey)
2Dusty Teal 3-4 Sleeve Kimono with Lace Trim - MDusty Teal 3-4 Sleeve Kimono with Lace Trim
3Lilac Floral Smocked 3-4 Ruffle Sleeve-Dress - SLilac Floral Smocked 3-4 Ruffle Sleeve-Dress
4Leopard - Print-Jumpsuit - (Heather - Grey) - LargeLeopard - Print-Jumpsuit - (Heather - Grey)
Product Name
Cell Formulas
RangeFormula
B1:B4B1=TRIM(TEXTBEFORE(A1,"-",-1))
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top