?
Solved

Excel formula

Posted on 2016-11-18
6
Medium Priority
?
36 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:RichardAtk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41892997
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41893012
Assumes data in A1

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))-1)
0
 

Author Closing Comment

by:RichardAtk
ID: 41893024
Perfect thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41893025
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41893041
...and you get my formula.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41893046
Indeed, you do. Author accepted my post while I was writing the comment agreeing with yours.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question