Remove last letter from column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kvc - 06 Sep 2007 19:06 GMT

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!

Reply to this Message

JW - 06 Sep 2007 19:19 GMT

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)

> 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
[quoted text clipped - 11 lines]
>
> Thanks!

Reply to this Message

kvc - 06 Sep 2007 19:22 GMT

> This will get rid of the A or B at the end along with the space before
> it. Assuming the value is in A5:
[quoted text clipped - 17 lines]
>
> - Show quoted text -

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

Reply to this Message

JW - 06 Sep 2007 19:28 GMT

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)

> > This will get rid of the A or B at the end along with the space before
> > it. Assuming the value is in A5:
[quoted text clipped - 19 lines]
>
> Thanks, but it didn't work... getting exactly what is in A5.

Reply to this Message

JW - 06 Sep 2007 19:30 GMT

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

> 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
[quoted text clipped - 26 lines]
> >
> > Thanks, but it didn't work... getting exactly what is in A5.

Reply to this Message

kvc - 06 Sep 2007 19:55 GMT

> Oops. Typo in my last post. Use:
> =IF(OR(RIGHT(A5,1)="B",RIGHT(A5,1)="A"),LEFT(A5,LEN(A5)-1),A5)
[quoted text clipped - 31 lines]
>
> - Show quoted text -

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!

Reply to this Message

Peo Sjoblom - 06 Sep 2007 19:23 GMT

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

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 11 lines]
>
> Thanks!

Reply to this Message