Are you saying that the final item (the US1234567-001 in your example) is
not always separated from the rest of your text by a comma? What about
things like LTD, Inc, etc... will a company name always have something like
one of those abbreviations after it, or could a company name be shown
without them?

Maybe Max's idea is the way to go. Do you have (or can you get) a full list
of companies that could be located at the beginning of your text?

--
Rick (MVP - Excel)


"Ranjit kurian" wrote in message
...
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.


"Ron Rosenfeld" wrote:

On Sat, 29 Nov 2008 01:05:00 -0800, Ranjit kurian
wrote:

I need a excel function to remove the numbers, decimals, coma, semicolon
and
words like "LTD", "INC" from the cells.

The name in the cells are not fixed, it keep changes

example :
my cell ("A1") contain the below details
Bank of America LTD Inc, US1234567-001.

the excel function should give the below answer
Bank of America

You can use a UDF (user defined function) but you need a more explicit
rule.

But Excel does not have feature to determine "words like 'LTD', 'INC'.
YOU
need to develop a list of words that you wish to test for.

In addition, you will need to develop an algorithm that can deal with
company
names that include numbers.

This requires, at least in part, a better understanding of the
variability in
your data.

For example;

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?

--ron