Remove last letter from column

K

kvc

Hi, I have a list of titles and some titles have a letter A or B at
the end.. is there a function/formula I can use to remove them if it
ends in A or B?

For example (my list):
Accounting Sr Mgr B
Accounts Payable Sr Mgr B
Ambulatory Plng Sr Prog Dir A

Need it to look like this:
Accounting Sr Mgr
Accounts Payable Sr Mgr
Ambulatory Plng Sr Prog Dir

Thanks!
 
J

JW

This will get rid of the A or B at the end along with the space before
it. Assuming the value is in A5:
=IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5)
 
K

kvc

This will get rid of the A or B at the end along with the space before
it. Assuming the value is in A5:
=IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5)







- Show quoted text -
Click to expand...

Thanks, but it didn't work... getting exactly what is in A5.
 
P

Peo Sjoblom

One possible way, with your data starting in A1, adapt to fit accordingly

=IF(OR(TRIM(RIGHT(A1,2))="B",TRIM(RIGHT(A1,2))="A"),LEFT(A1,LEN(TRIM(A1))-2),A1)

use that in a help column, copy down as long as needed, then copy and paste
special as values in place. Make sure it works OK and if needed delete the
original data
 
J

JW

Works perfectly fine for me here. What is the exact value at the
end? Is it a single space follwed by an A or B (as in your example
data) or is it simply an A or B directly at the end of the title? The
formula I posted looks for a single space followed by an A or B. If
there is no space, use this.
=IF(OR(RIGHT(A5,)="B",RIGHT(A5,)="A"),LEFT(A5,LEN(A5)-1),A5)
 
J

JW

Oops. Typo in my last post. Use:
=IF(OR(RIGHT(A5,1)="B",RIGHT(A5,1)="A"),LEFT(A5,LEN(A5)-1),A5)
 
K

kvc

Oops. Typo in my last post. Use:
=IF(OR(RIGHT(A5,1)="B",RIGHT(A5,1)="A"),LEFT(A5,LEN(A5)-1),A5)





- Show quoted text -
Click to expand...

I'm so SORRY.. the first solution WORKED! We exported this list from
another database and every title had about 20 spaces after the title..
so I used trim to clean that up and it worked! Thanks for everyone's
help! Much appreciated!
 
Top