Remove last letter from column

  • Thread starter Thread starter kvc
  • Start date Start date
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!
 
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)
 
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.
 
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
 
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)
 
Oops. Typo in my last post. Use:
=IF(OR(RIGHT(A5,1)="B",RIGHT(A5,1)="A"),LEFT(A5,LEN(A5)-1),A5)
 
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!
 
Back
Top