Delete numbers in cell

**Solrick** · 06-21-2012, 04:43 AM

Hello,

I have a long list of cells like this:

BRITISH AIRWAYS FI
ONTEX IV 7.5% 04/15/18 /EUR/
DUBAI HOLDING 6% 2/1/17 /GBP/
BES FINAN 3% 05/19/15 /EUR/
UPCB FINA 6.375% 07/01/20 /EUR
BANCO ESPIRITO 1.625% 04/15/13
AGROKOR D 10% 12/07/16 /EUR
CET 21 SP 9% 11/01/17 /EUR/
EUROPCAR 9.375% 04/15/18 /EUR
LAFARGE S 5.375% 11/29/18 /EUR
SPCM SA 8.25% 06/15/17 /EUR/
EUROPCAR BOND FUNDING LTD
TUI AG V/R /PERP/EUR/
UNITYMEDIA GMBH 9.625% 12/01/19/EUR
UNITYMEDIA H 9.5% 3/15/21/EUR
UNITYMEDI 7.5% 03/15/19 /EUR/
HEIDELBER 9.25% 04/15/18 /EUR/
MOL MAGYAR 3.875% 10/5/15/EUR/

I am searching for a formula or macro that only shows the name of the company. Can someone please help me?

Thanks,
Rick

**npamcpp** · 06-21-2012, 04:52 AM

can you describe the rules in simple words that identify what constitutes the company name? Is it UNITYMEDIA or UNITYMEDIA H or UNITYMEDIA GMBH? Is it AGROKOR D or just AGROKOR? If you can come up with a logical explanation, then someone here will be able to come up with a function or macro.

But if the recognition of the company name involves human interaction, then you're pretty much out of luck.

**Fotis1991** · 06-21-2012, 04:54 AM

Hi Rick and welcome to the forum

If your words are always in the left side of the string, then you can use this Array formula(Control+Shift+Enter) NOT just Enter.

Please Login or Register  to view this content.

**Solrick** · 06-21-2012, 04:56 AM

It is the name until the start of the percentage. So it should be AGROKOR D.
I think I have to find a way to delete the right part of the cell when the numbering is starting.

**npamcpp** · 06-21-2012, 05:17 AM

what about

TUI AG V/R /PERP/EUR/

there's no % sign. Is that the name then? Or is it TUI? Or TUI AG?

My point is, there will probably not be a formula, since it's almost impossible to describe the logic. One has to be familiar with the brand names to recognise where a company name ends.

**npamcpp** · 06-21-2012, 05:20 AM

Fotis, I can't get your formula to produce anything but #N/A (yes, I array entered). Can you post a file where it works as you expect?

**Solrick** · 06-21-2012, 06:49 AM

Thanks for the help everybody but I think i have found the best possible solution.
I have searched with FIND function for the first number to appear. Then with LEFT function I get the correct name in 99% of the cases, except for the ones you mention above.

But anyway,
thanks for the help