One way:

=LEFT(SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""))+1)-1)

Doesn't look great and I'm sure there's a better way ... but built from
first principles

The SUBSTITUTE(SUBSTITUTE(... gets rid of the "The " and the comma. The
remainder is working out where the second space is, taking 1 off to give the
length and then uses LEFT to extract the data. Unfortunately, it will fail
if there is no second space. You would therefore need to extend the
equation to check for this condition.

You can count the spaces:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

So, you could test for the mini,um for the equation to work:

=IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))3,"the long equation","the shorter
version")

I'm struggling to work out the shorter version of the equation but hopefully
this will give you something to start with.

Regards

Trevor


"Help?" wrote in message
...
I currently have a excel macro that pulls information from the excel
columns
and adds it to the filename. Then, the macro will convert to csv file. A
column exists in the file called EMPLOYER NAME. An individual will insert
a
company's name in this column. This will be pulled into the filename.
However, if a company is ABC Company, Inc. - the comma in the filename
throws
our printing system into a loop. So, I use the equation =LEFT(A2,FIND("
",A2))&MID(A2,FIND(" ",A2)+1,0) to only pull in the first portion of the
name
into another column and then insert the new name into the filename. Works
good, but I am now running into the problem of the name being The ABC
Company, Inc. The comma is not present, but the filename is The. Does
anyone
know an equation I could use that would only pick out ABC Company and
leave
The and the comma out?