# 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?
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Assuming that data is on cell A1, put this formula on cell B1:

=LEFT(A1,5)

jppinto
0
Commented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author 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
Commented:
Sorry, this is the correct formula:

=LEFT(A1,6)
0
VBA 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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.