asked on

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?

jppinto

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

=LEFT(A1,5)

jppinto

jppinto

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

ASKER CERTIFIED SOLUTION

Tracy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

r270ba

ASKER

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 '-'

jppinto

Sorry, this is the correct formula:

=LEFT(A1,6)

Tracy

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