Excel Substring Based On Last Characters of String

I have some data that may or may not have a 'P', 'SA', 'M' appended to the end of it.  If it does, then I need to take a substring of the data from the appended suffix all the way back to the '-' in front of it.

Examples:

10-017-SA needs to be 10-017
10-114-01P needs to be 10-114
10-115-P needs to be 10-115

Any ideas?
r270baAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TracyConnect With a Mentor VBA DeveloperCommented:
Try this:

=IF(OR(RIGHT(A1,1)="P",RIGHT(A1,1)="M",RIGHT(A1,2)="SA"),LEFT(A1,FIND("-",A1,4)-1),A1)
Book2.xls
0
 
jppintoCommented:
Assuming that data is on cell A1, put this formula on cell B1:

=LEFT(A1,5)

jppinto
0
 
jppintoCommented:
LEFT formula will get you only the 5 characters counting from the left, in this first case it will return just the 10-017 part of the string.

jppinto
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
r270baAuthor Commented:
Sorry but my example just happened to have all the characters I needed as length of 5.  The "front end" of the string will be different lengths.  I really need to go from the suffix and work my way back to the '-'
0
 
jppintoCommented:
Sorry, this is the correct formula:

=LEFT(A1,6)
0
 
TracyVBA DeveloperCommented:
Mine will return the value of A1 if it doesn't end in P, M or SA.  If it does end it one of those, then it will return the left portion of the cell value upto the second hypen.  This assumes the first hyphen is character 3.

If the first hypen is a variable spot, then this will do it:
=IF(OR(RIGHT(A1,1)="P",RIGHT(A1,1)="M",RIGHT(A1,2)="SA"),LEFT(A1,FIND("-",A1,FIND("-",A1,1)+1)-1),A1)
Book2.xls
0
All Courses

From novice to tech pro — start learning today.