# excel 2003 (left,mid,right) formula ?

#### johnny52

##### Active Member
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### oldbrewer

##### Well-known Member
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

##### Well-known Member
=left(a1,search("_",a1,1)-1)

=find("_",a1)

#### oldbrewer

##### Well-known Member
I was trying to keep it really simple so that you could follow the logic.......smiles

#### oldbrewer

##### Well-known Member
=left(a1,find("_",a1)-1)

#### oldbrewer

##### Well-known Member
mine is 3 less characters !!!

#### johnny52

##### Active Member
perfect rallcorn.......thanks so much

and to everyone who responded.

#### johnny52

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

#### rallcorn

##### Well-known Member
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!!

Replies
5
Views
451
Replies
10
Views
333
Replies
5
Views
360
Replies
11
Views
191
Replies
9
Views
184

1,195,721
Messages
6,011,299
Members
441,601
Latest member
mgdll

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back