Help me use =Right or more suitable text formula

[kate s]

I appreciate the help with this, which will help me learn. I know I can do this with Query from Table, but I'd like to learn it by formula. I figured out how to extract the numbers within the ( parens ) using left and find. Now, I'd like to work out how to remove only the company names via a formula. I've tried right and mid but my logic is not working.

Thank you so much for any help.

Book1
	B	C	D
3	Companies	Co #	Co Name
4	(124) Quest Diagnostics	(124)	stics
5	(559) Armor Blood Draw	(559)	o
6	(59) Excess White Cell Co	(59)
7			My logic does not work
Sheet1

Cell Formulas
Range	Formula
D4	D4	=RIGHT(B4,FIND(")",B4,2)*1)
D5	D5	=MID(B5,FIND(")",B5,1)*2,1)
C4:C6	C4	=LEFT(B4,FIND(")",B4)*1)

 

[Alex Blakenburg]

Here are some options, there are others.
By the way your "*1" in column C doesn't do anything and shouldbe left out.

Book1
	B	C	D	E
3	U	Co #	Co Name	Comment
4	(124) Quest Diagnostics	(124)	Quest Diagnostics	Right
5	(559) Armor Blood Draw	(559)	Armor Blood Draw	Right and leveraging off Co#
6	(59) Excess White Cell Co	(59)	Excess White Cell Co	Replace and leveraging off Co#
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=LEFT(B4,FIND(")",B4)*1)
D4	D4	=RIGHT(B4,LEN(B4)-FIND(")",B4)-1)
D5	D5	=RIGHT(B5,LEN(B5)-LEN(C5)-1)
D6	D6	=REPLACE(B6,1,LEN(C6)+1,"")

 

[jolivanes]

=TRIM(MID(B4, FIND(")", B4) + 1, 99))

Alex was a little faster.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you happen to have the latest 'TEXT' functions, there are even easier ways.

22 09 15.xlsm
	B	C	D
3	Companies	Co #	Co Name
4	(124) Quest Diagnostics	(124)	Quest Diagnostics
5	(559) Armor Blood Draw	(559)	Armor Blood Draw
6	(59) Excess White Cell Co	(59)	Excess White Cell Co
Split Text

Cell Formulas
Range	Formula
C4:C6	C4	=TEXTBEFORE(B4," ")
D4:D6	D4	=TEXTAFTER(B4," ")

 

[Peter_SSs]

If you happen to have the latest 'TEXT' functions, ..

.. and if you don't, then here is yet another way to get the Co name

22 09 15.xlsm
	B	C	D
3	Companies	Co #	Co Name
4	(124) Quest Diagnostics	(124)	Quest Diagnostics
5	(559) Armor Blood Draw	(559)	Armor Blood Draw
6	(59) Excess White Cell Co	(59)	Excess White Cell Co
Split Text (2)

Cell Formulas
Range	Formula
C4:C6	C4	=LEFT(B4,FIND(")",B4))
D4:D6	D4	=SUBSTITUTE(B4,C4&" ","")

 

[kate s]

Thank you, everyone. All were GREAT solutions. I appreciate you all shedding insight on the many ways to accomplish this task.

 

[Alex Blakenburg]

You're welcome. Glad we could help.