Removing "The" from company names
 

I am trying to sort Company names alphabetically and need a formula in excel
that can remove the initial "The" from the company name before doing this.
Any ideas?

Removing "The" from company names
 

Is this any good?

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

Mike

"jamesea" wrote:

I am trying to sort Company names alphabetically and need a formula in excel
that can remove the initial "The" from the company name before doing this.
Any ideas?

Removing "The" from company names
 

Sorry, Just realised that the previous formula will remove a 'The' from the
middle of the name, this one gets around that.

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

Mike

"jamesea" wrote:

I am trying to sort Company names alphabetically and need a formula in excel
that can remove the initial "The" from the company name before doing this.
Any ideas?

Removing "The" from company names
 

Just a slight twick to Mike's excellent solution:

=IF(LEFT(A1,4)="The ",TRIM(SUBSTITUTE(A1,"The" ,"",1)),A1)

Some company names may begin with The.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel



"jamesea" wrote in message
...
I am trying to sort Company names alphabetically and need a formula in
excel
that can remove the initial "The" from the company name before doing this.
Any ideas?