Excel formula

Hi

I want to split product codes which are different lengths but always have a - in the same place.  I want to create a field which is a short code version and have a formula to do the split rather than sorting and doing text to columns the data. It's always the information before the second -

So the data below is in column B
BSR-G19-BE-L
ZR-17-OL
JWS-27-BR-10

In column C I would like
BSR-G19
ZR-17
JWS-27

Many thanks
RichardAtkAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Assuming value in B3, use formula in C3:

=LEFT(SUBSTITUTE(B3,"-","|",2),FIND("|",SUBSTITUTE(B3,"-","|",2),1)-1)

This replaces second occurence of "-" with "|" and then pulls everything to left of "|"

"|" is shift plus key next to z
0
 
Martin LissOlder than dirtCommented:
Assumes data in A1

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))-1)
0
 
RichardAtkAuthor Commented:
Perfect thanks
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Rob HensonFinance AnalystCommented:
Yep, looking at it don't need the SUBSTITUTE part in the first parameter of the LEFT function.

So just:

=LEFT(B3,FIND("|",SUBSTITUTE(B3,"-","|",2),1)-1)
0
 
Martin LissOlder than dirtCommented:
...and you get my formula.
0
 
Rob HensonFinance AnalystCommented:
Indeed, you do. Author accepted my post while I was writing the comment agreeing with yours.
0
All Courses

From novice to tech pro — start learning today.