I currently asked the below question and received the below equation along
with several other equations. It works great, however, it will not accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

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

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation along
with several other equations. It works great, however, it will not accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

Gary,
It's still not working for any of the cases like:
The Volunteer Inc.
Volunteer
Volunteer, Inc.
The Volunteer, Inc.

It's not acting like it wants to pull A2 into the other column when I use
the below equation.

"Gary''s Student" wrote:

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

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation along
with several other equations. It works great, however, it will not accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

Oh sure, you can use that very plain and simple formula...or you could use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

It will not work for the last two cases you listed because they don't contain
commas.

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

will remove the The and also everything to the right of a comma including
the comma.

Just make sure you version of Excel supports the SUBSTITUTE() function.
--
Gary's Student


"Help?" wrote:

Gary,
It's still not working for any of the cases like:
The Volunteer Inc.
Volunteer
Volunteer, Inc.
The Volunteer, Inc.

It's not acting like it wants to pull A2 into the other column when I use
the below equation.

"Gary''s Student" wrote:

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

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation along
with several other equations. It works great, however, it will not accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

Is
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")
any better??
--
Gary's Student


"Help?" wrote:

Gary,
It's still not working for any of the cases like:
The Volunteer Inc.
Volunteer
Volunteer, Inc.
The Volunteer, Inc.

It's not acting like it wants to pull A2 into the other column when I use
the below equation.

"Gary''s Student" wrote:

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

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation along
with several other equations. It works great, however, it will not accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

It would have to be for only cell AW2. Any suggestions? I am not familiar
with this aspect.

"Help?" wrote:

What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

I think the last suggestion from Gary's Student was the best solution,
unless you're looking to only use the source cell and have your results in
that cell.
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")

"Help?" wrote in message
...
What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could
use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in
message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma.
It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The".
Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

It didn't work either. Is their a macro that could replace just the comma in
cell AW2 with a simple space?

"PCLIVE" wrote:

I think the last suggestion from Gary's Student was the best solution,
unless you're looking to only use the source cell and have your results in
that cell.
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")

"Help?" wrote in message
...
What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could
use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in
message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma.
It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The".
Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

I'm sure someone here will be able to explain this as I am not sure why it
works. However, the code below appears to work if you want to remove
commas, If you want a space in place of a comma, just add a space between
the two quotes.


Sub RemoveCommas()

n = Range("AW2").Replace(",", "")

End Sub


Regards,
Paul

"Help?" wrote in message
...
It didn't work either. Is their a macro that could replace just the comma
in
cell AW2 with a simple space?

"PCLIVE" wrote:

I think the last suggestion from Gary's Student was the best solution,
unless you're looking to only use the source cell and have your results
in
that cell.
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")

"Help?" wrote in message
...
What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could
use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in
message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma.
It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below
equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The".
Can
anyone help in finding an equation that would accomodate for all
three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

Hey guys:
This is still not working.

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma. It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The". Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

Sub one_last_try()
Dim r As Range
Set r = Range("AW2")
With r
.Value = WorksheetFunction.Substitute(.Value, ",", " ")
End With
End Sub
--
Gary's Student


"Help?" wrote:

It didn't work either. Is their a macro that could replace just the comma in
cell AW2 with a simple space?

"PCLIVE" wrote:

I think the last suggestion from Gary's Student was the best solution,
unless you're looking to only use the source cell and have your results in
that cell.
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")

"Help?" wrote in message
...
What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could
use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in
message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma.
It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The".
Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?

YOU ARE AWESOME!! IT WORKED LIKE A CHARM. THANK YOU SO MUCH.

"Gary''s Student" wrote:

Sub one_last_try()
Dim r As Range
Set r = Range("AW2")
With r
.Value = WorksheetFunction.Substitute(.Value, ",", " ")
End With
End Sub
--
Gary's Student


"Help?" wrote:

It didn't work either. Is their a macro that could replace just the comma in
cell AW2 with a simple space?

"PCLIVE" wrote:

I think the last suggestion from Gary's Student was the best solution,
unless you're looking to only use the source cell and have your results in
that cell.
=SUBSTITUTE(LEFT(A2,FIND(",",A2 & ",",1)-1),"The ","")

"Help?" wrote in message
...
What about a Replace macro that would be just for commas?

"PCLIVE" wrote:

Oh sure, you can use that very plain and simple formula...or you could
use
this one.

=LEFT(IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-5)),FIND(",",IF(LEFT(A2,3)="The",RIGHT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-4)))-1)



"Gary''s Student" wrote in
message
...
=SUBSTITUTE(LEFT(A2,FIND(",",A2,1)-1),"The ","")

This will remove the comma and everything to the right of the comma.
It
will also remove
The
as coded it looks for a capital T and a trialing blank
--
Gary's Student


"Help?" wrote:

I currently asked the below question and received the below equation
along
with several other equations. It works great, however, it will not
accomodate
for those employer names that do not have commas or the word "The".
Can
anyone help in finding an equation that would accomodate for all three
cases?

__________________________________________________ _______



=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma
--
Gary's Student


"Help?" wrote:

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?