# Create a formula to remove last two characters from a cell if the 2nd character is a space

1. ## Create a formula to remove last two characters from a cell if the 2nd character is a space

Hello, I am working with a data set of over 6,000 rows and am looking for a way to reduce some of the extra unique lines.
For Example: Some of the lines are exactly the same but end with a " A" or " B" or " 1" or " 2" ect
Not all lines end with those characters they are just to indicate the same Name has been used multiple times.

Cat
Black Dog
Dog 1
Cat A
Frog
Cat 2
Cat 3
Fish
Black Dog A
Brown Dog
Brown Dog C

Is there a formula that will remove that last two characters if and only the 2nd to last character is a space.
Thanks,
Kevin

2. ## Re: Create a formula to remove last two characters from a cell if the 2nd character is a s

Try,

=IF(1-ISERR(FIND(" ",RIGHT(A1,2))),LEFT(A1,LEN(A1)-2),A1)

Regards

3. ## Re: Create a formula to remove last two characters from a cell if the 2nd character is a s

Thank you, this is exactly what I needed.

4. ## Re: Create a formula to remove last two characters from a cell if the 2nd character is a s

Or this
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1