Trim left, but only if the first 3 character start with "The"

**firstsgt1978** · 03-25-2014, 11:17 AM

Hello

banging my head on the key board..............

I have a column of company names - if the name begins with "The" I would like to trim the cell

ie..."The Sherwin-Williams Company" would become "Sherwin-Williams Company"

I thought this would work, but there is an error I cannot find

=LEFT(A1,LEN(A1)-(EXACT(RIGHT(A1),"S")))&IF(RIGHT(A1,1)="The")

Any help would be greatly appreciated.

**AlKey** · 03-25-2014, 11:20 AM

Try this

=SUBSTITUTE(A1,"The ","")

**newdoverman** · 03-25-2014, 11:24 AM

This might work for you:

Formula:

Please Login or Register  to view this content.

**firstsgt1978** · 03-25-2014, 11:34 AM

Hello

Thanks for the reply - my concern is would this not remove all of the "The" from a given cell - I only want "The remove if it is the first 3 characters

**firstsgt1978** · 03-25-2014, 11:37 AM

Newdoverman - worked perfect! Thanks!

Comment about the concern was for AlKey

Issue is resolved!

**AlKey** · 03-25-2014, 11:37 AM

Then this

=IF(LEFT(A1,3)="The",SUBSTITUTE(A1,"The ",""),A1)

**newdoverman** · 03-25-2014, 11:47 AM

Thank you for the feedback.

**AlKey** · 03-25-2014, 11:47 AM

Or this

=IF(LEFT(A1,3)="The",REPLACE(A1,1,4,""),A1)

Trim left, but only if the first 3 character start with "The"

LinkBack

Thread Tools

Rate This Thread

Display

Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Re: Trim left, but only if the first 3 character start with "The"

Thread Information

Users Browsing this Thread

Similar Threads

replace all defined range names in the sheet that start with "Street" to "Road"

Any advantage to "trim" over "find/replace" for eliminating spaces?

Adding multiple "find" with "left" /"Right"

Rearrange format with "TRIM" and "SUBTRACT" functions

HELP on "left","right","find","len","substitute" functions

Bookmarks

Bookmarks

Posting Permissions