I need to omit "_(inconsistent amount of letters)_(three numbers"

[splate]

I have a 65,000 cell column of entries like the ones below:


ACE_Casualty_XL_001
ACE_European_D&O_BARC_001
ACE_Casualty_HON_001
ACE_European_D&O_HLT_001

I need them to read:

ACE_Casualty
ACE_European_D&O
ACE_Casualty
ACE_European_D&O

In short, I need to omit the last three numbers, the underscore before that, the inconsistent amount of numbers before that, and the last underscore (_HLT_001) for example. Help!

PS I'm a brand new 18 year old intern, and I'm struggling with the job. Please excuse the inability in formatting. Excel 2003 is my platform, and I need it to be done by formatting.

Thank you all.

[daddylonglegs]

If your data starts at A2 try putting this formula in B2

=LEFT(A2,FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))-1))-1)

then copy it down the column

[splate]

Thank you.

It seems to work with most, but some words are omitted incorrectly. For example:

ACE_Casualty_002
Advisen_Casualty_003
ACE_Casualty_UCB_001
US_Public_D&O_RY_002
ACE_Casualty_ABB_001
ACE_Casualty_006
ACE_Casualty_BBL_001
ACE_Casualty_BHP_002

Turns into:

ACE
Advisen
ACE_Casualty
US_Public_D&O
ACE_Casualty
ACE
ACE_Casualty
ACE_Casualty

So some work and some don't.

I really appreciate this. Thank you.

[splate]

I didn't realize that not all contained the "_letters"

I apologize

[daddylonglegs]

OK, that's going to be a little tricky......

How can you distinguish between "letters" that you want to omit and "words" like "casualty" that you want to keep. I thought of using case or length but D&O is upper case and short so that won't work.

Is there any other pattern, all your examples without letters have "Casualty" immediately before, would that always be the case?

[splate]

This may help a little. I just noticed that there is a column adjacent to this one that contains copies of the letters that must be omitted. For example:

RIG US_Public_D&O_RIG_001
Advisen_Public_Casualty_002
Advisen_Casualty_002
ORI ACE_European_D&O_ORI_001
IHG ACE_European_D&O_IHG_001

Must become:

US_Public_D&O
Advisen_Public_Casualty
Advisen_Casualty
ACE_European_D&O
ACE_European_D&O

Helpful?

[splate]

That wasn't very cleanly posted. Let me try again:

RIG US_Public_D&O_RIG_001
Advisen_Public_Casualty_002
Advisen_Casualty_002
ORI ACE_European_D&O_ORI_001
IHG ACE_European_D&O_IHG_001

[splate]

Did it again. Sorry.

[splate]

RIG .............US_Public_D&O_RIG_001
..............Advisen_Public_Casualty_002
..............Advisen_Casualty_002
ORI ..............ACE_European_D&O_ORI_001
IHG ..............ACE_European_D&O_IHG_001

With the dots indicating a column change.

[daddylonglegs]

OK assuming the letters are in A2 and the data in B2 try this formula

=LEFT(B2,LEN(B2)-LEN(A2)-5)

[splate]

Almost there. Thank you.

Occasionally, the last letter is omitted as well ("Casualt" appears) and the underscore is left sometimes (D&O_).

Examples of the result after the most recent formula:

HLT ......ACE_European_D&O_HLT_001 ......ACE_European_D&O
KESA .......ACE_European_D&O_KESA_001 .......ACE_European_D&O
BCS .......ACE_European_D&O_BARC_002 ......ACE_European_D&O_
BCS ......ACE_European_D&O_BARC_003 ......ACE_European_D&O_
EON .......ACE_Casualty_EON_001 .......................ACE_Casualty
EON ......ACE_Casualty_EON_002 ......................ACE_Casualty
EON .......ACE_Casualty_EON_003 ......................ACE_Casualty
TKA .......ACE_Casualty_TKA_001 ......................ACE_Casualty
HOT .......ACE_Casualty_HOT_001 ......................ACE_Casualty
MAN .......ACE_Casualty_002 .......................ACE_Casu

[daddylonglegs]

OK, I thought the letters in column A would match the letters in column B but in entries 3 and 4 you have "BCS" in A but "BARC" in B. Also in the last example you have "MAN" in column A but no letters in column B, I assumed that column A would be empty if the letters weren't present iin column B......?

[splate]

Crap. It's supposed to be. Well, at least THAT isn't my fault. I'll bring it to the attention of my supervisor. Thank you so much. And just so you don't get the wrong impression, I'm interning to learn. I thought I had a pretty solid understanding of Excel, but now I'm being introduced to all kinds of neat stuff! PivotTable is great!

Thank you again. I really appreciate it.