EXCEL: REMOVE PRODUCT SIZE / REMOVE SUFFIX

Question

Hi,

My product variants have the size at the end of each variant, however I want to remove the size and just keep product code. I have tried substitute but in Excel version I am using it will only let me enter certain amount. Is there another way? I am using a MAC.

E.G

CWNOS01RPLN20-XXS - would become CWNOS01RPLN20

CWNOS01RPLN20-XS - would become CWNOS01RPLN20

CWNOS01RPLN20-S - would become CWNOS01RPLN20

CWNOS01RPLN20-M - would become CWNOS01RPLN20

CWNOS01RPLN20-L - would become CWNOS01RPLN20

CWNOS01RPLN20-XL - would become CWNOS01RPLN20

CWNOS51CPLN20-18 - would become CWNOS51CPLN20

CWNOS51CPLN20-20 - would become CWNOS51CPLN20

CWNOS51CPLN20-22 - would become CWNOS51CPLN20

CWNOS51CPLN20-24 - would become CWNOS51CPLN20

CWNOS51CPLN20-26 - would become CWNOS51CPLN20

CWNOS51CPLN20-28 - would become CWNOS51CPLN20

Answer 1

Split the text into columns using "-" as delimiter. Please follow this article: https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Andreas,

Answer 2

Hi Joe

Try the formula as indicated in the picture below. Then simply adapt it/change the ranges to the real scenario in your side.

=TRIM(MID(A2,SEARCH("-",A2)+1,5))

Nevertheless, you may also try other automatic and effective methods with features within excel

The following videos will provide you with the instructions.

https://www.youtube.com/watch?v=1KimYFzET1w&t=181s

https://www.youtube.com/watch?v=_py2LLQiYvw

I hope this helps you and gives a solution to your question

Regards

Jeovany

Answer 3

Brilliant - thanks so much Jeovany.

Answer 4

Sorry Jeovany, this just keeps the size in column b - column b i would want to be CWNOS01RPLN20 and not the size?

Answer 5

Fair enough

Here is the formula =TRIM(LEFT(A2,SEARCH("-",A2)-1))