excel 2003 (left,mid,right) formula ?

[johnny52]

Actuarial_16201_CC
Actuarial_16201_CC
Corporate Accounting_10100_CC

is there a way to use a mid,left or right formula to just have the word "actuarial"
or "corporate accounting" to appear....I understand that the words are not of equal length
so I keep dropping off part of the word

 

[oldbrewer]

Count the letters in front of the underscore. To do this search for the position of the underscore.
actuarial_16201_cc

position of first underscore = 10 10-1 =9 left (a1,9) = actuarial (assumes word is in cell A1)

 

[rallcorn]

=left(a1,search("_",a1,1)-1)

 

[oldbrewer]

=find("_",a1)

 

[oldbrewer]

ADVERTISEMENT

I was trying to keep it really simple so that you could follow the logic.......smiles

 

[oldbrewer]

=left(a1,find("_",a1)-1)

 

[oldbrewer]

ADVERTISEMENT

mine is 3 less characters !!!

 

[johnny52]

perfect rallcorn.......thanks so much

and to everyone who responded.

 

[johnny52]

I appreciate the help...........it worked with rallcorn response........your solution worked as well

 

[rallcorn]

mine is 3 less characters !!!

Yes, but "find" is case sensitive and "search" is not. That doesn't make a difference in this particular scenario of looking for "_" . . . but it makes a BIG difference when you're looking for words and you don't input the case in the "Find" string the exact same way it appears in the cell you're checking!!