Filename Problem

[Guest]

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

 

[Guest]

=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

 

[Guest]

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.

 

[PCLIVE]

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)

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[PCLIVE]

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 ","")

 

[Guest]

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

 

[PCLIVE]

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

 

[Guest]

Hey guys:
This is still not working.

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)

 

[Guest]

Sub one_last_try()
Dim r As Range
Set r = Range("AW2")
With r
.Value = WorksheetFunction.Substitute(.Value, ",", " ")
End With
End Sub

 

[Guest]

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