On Sat, 29 Nov 2008 08:18:00 -0800, Ranjit kurian
wrote:

1. Will there always be a final comma, after which everything can be removed?
2. Can numbers (and punctuation) prior to the comma be assumed to be part of
the company name?

answer: no, nothing is constant here, keep changes.

And if you think its not possibe to write any formula when the things are
not constant(fixed), then i will use your formula where i will have a 'final
comma' and punctuation are found as you said below.

Using the "rules" I mentioned, you could use this UDF.

To enter the UDF <alt-F11 opens the VB Editor. Ensure your project is
selected in the project explorer window, then Insert/Module and paste the code
below into the window that opens.

The third line of the code is a pipe-delimited list of the various suffixes to
the company name that you wish to remove. You will have to add on whatever
else there is besides LTD and INC

To use this, enter a formula of the type

=CorpName(cell_ref)

where cell_ref is the address containing the name to be processed.

============================
Option Explicit
Function CorpName(str As String) As String
Const sSuffix = "LTD|INC"
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "(^[\s\S]+?)\s+\b(" & _
sSuffix & ")\b[^,]*,?[^,]*$"
CorpName = re.Replace(str, "$1")
End Function
=================================
--ron