Extracting using the RIGHT function (XP)

Topic

New Reply

What I am attempting to do is extra the information in the parenthesis in order to sort by job classification. I thought that the RIGHT Function might be suitable, but it gives me the workers first name along with the job classification. Is there a method of being able to extract what is in the parenthesis in order to do the sort?

Reply | Quote

Replies

Try

=MID(A1,FIND(“(“,A1)+1,FIND(“)”,A1)-FIND(“(“,A1)-1)

in B1, and fill down as far as needed.

Reply | Quote

Thanks Hans. This works well. When you have sometime would mind explaining the functioning of FIND(“(” where it is used.

Reply | Quote

FIND(a, looks for the first occurrence of (the value of) a in (the value of) b, and returns the position of this first occurrence. So for example FIND(“o”, “Woody”) returns 2 because the first occurrence of “o” in “Woody” is in the second character. And FIND(“o”, “Gates”) returns an error because “o” is not found in Gates.

The search is case-sensitive; if you want a case-insensitive search use SEARCH instead of FIND.

If you look up FIND and SEARCH in the Excel help, you’ll find more detailed information.

Reply | Quote

Another option :

=SUBSTITUTE(REPLACE(A1,1,FIND(“(“,A1),””),”)”,””)

Or this one, only in saving some keystrokes

=SUBSTITUTE(REPLACE(A1,1,FIND(“(“,A1),),”)”,)

Regards
Bosco

Reply | Quote

Hi Bosco

Is it working at your end? I tried the formula provided but its return #VALUE….. not sure why

Thanks

regards,

Reply | Quote

Are you sure that you entered the formula correctly? I have attached your workbook with both versions of bosco_yip’s formula.

Reply | Quote