Have a text string in a specific format, that I'd like split up into 3 adjacent cells based on formulas

[freeb1893]

I have a name variation I'm working with that is like the following example:

"Project Type - (Main Component) Additional Information"

I want process the above quoted text string into 3 cells. I was all the text before the dash in 1 cell, all the text in the () is a 2nd cell, and everything after the ") " in a 3rd cell.

Anyone know the formulas I need in my 3 cells to process this text string in such a way? Thanks in advance!

 

[Roderick_E]

Hi,
If the user always uses the syntax you describe:

Let's say for this example, the original data is in cell B2 and you want the splits to happen in C2, D2 and E2
C2 code should be =TRIM(LEFT(B2,FIND("-",B2)-1))
D2 code should be =TRIM(MID(B2,FIND("(",B2),FIND(")",B2)-FIND("(",B2)+1))
E2 code should be =TRIM(MID(B2,FIND(")",B2)+1,999))

 

[JoeMo]

Here's another way:

Excel Workbook
	A	B	C	D
1	Project Type - (Main Component) Additional Information	Project Type	Main Component	Additional Information
Sheet1

 

[jtakw]

Hi,

Think this is what you mean:

Book1
	A	B	C	D
1	"Project Type - (Main Component) Additional Information"	Project Type	Main Component	Additional Information
Sheet10

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,2,FIND("-",A1)-2))
C1	=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
D1	=SUBSTITUTE(TRIM(MID(A1,FIND(")",A1)+1,255)),CHAR(34),"")

Results excludes the quote marks, and brackets.

 

[Roderick_E]

search is better than find as search is not case specific. Can be lower or upper case.

 

[jtakw]

I know the difference between SEARCH and FIND, but what's the Lower and/or Upper case of " , ( , and ) ?

I use SEARCH when there's a possible Upper/Lower case.

 

[Roderick_E]

Wasn't scolding anyone, just informing those who might not be aware of the difference. Obvious my answer didn't use search AND was clunkier than yours and JoeMos answer. No offense meant.

 

[jtakw]

My apologies, didn't mean to sound like I was "snapping back" neither, but it's Good that you pointed out the difference between the SEARCH and FIND functions.

And I might have misunderstood OP, maybe the quotes before and after the Text string is Not in the Real data but just in the post to show us the Text string, in which case, my formulas will need to be modified.