Extract Words after String Variable

[jayjavina]

Hello!
I need some assistance please in putting these characters into 3 different columns
"Communication/ Call Handling Skills;#Will (Behavioral Issues);#System Issue/ Machine Issues"

It should come out as:
Col1 = Communication/ Call Handling Skills
Col2 = Will (Behavioral Issues)
Col3 = System Issue/ Machine Issues

I tried using... but I cant pass the middle one and last one.
=LEFT(A1,(FIND(";",A1,1)-1))

Thanks in advance

 

[jtakw]

Hi,

If the Start and End quote marks are actually part of the Text string, will need slight modification to formula, try this, copied across as far as you need:

Book3.xlsx
	A	B	C	D	E
1	Communication/ Call Handling Skills;#Will (Behavioral Issues);#System Issue/ Machine Issues	Communication/ Call Handling Skills	Will (Behavioral Issues)	System Issue/ Machine Issues
Sheet790

Cell Formulas
Range	Formula
B1:E1	B1	=TRIM(MID(SUBSTITUTE(";#"&$A1,";#",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

 

[jayjavina]

Hi,

If the Start and End quote marks are actually part of the Text string, will need slight modification to formula, try this, copied across as far as you need:

Book3.xlsx
	A	B	C	D	E
1	Communication/ Call Handling Skills;#Will (Behavioral Issues);#System Issue/ Machine Issues	Communication/ Call Handling Skills	Will (Behavioral Issues)	System Issue/ Machine Issues
Sheet790

Cell Formulas
Range	Formula
B1:E1	B1	=TRIM(MID(SUBSTITUTE(";#"&$A1,";#",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

Thanks brother! Appreciate the help. will apply that in my report!

 

[Michael M]

Maybe this

Book1
	A	B	C
1	Communication/ Call Handling Skills;#Will (Behavioral Issues);#System Issue/ Machine Issues	Communication/ Call Handling Skills
2		#Will (Behavioral Issues)
3		#System Issue/ Machine Issues
Sheet2

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND(";",A1)-1)
B2	B2	=SUBSTITUTE(MID(SUBSTITUTE(";"&A1&REPT(" ",6),";",REPT(",",255)),2*255,255),",","")
B3	B3	=MID(A1, FIND(";", A1, FIND(";", A1)+1)+1,256)

 

[jtakw]

You're welcome, thanks for the feedback.

 

[Peter_SSs]

You could also do this manually pretty quickly

- Select the column
- Text to Columns (on Data ribbon tab)
- Delimited - Next
- Set it like this

- Next & set first destination cell if required
- Finish


.. or the same as above but with vba
Assuming data in column A and results in B:D

Sub Split_Text()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).TextToColumns Range("B1"), xlDelimited, , True, False, True, False, False, True, "#"
End Sub

 

[jayjavina]

Maybe this

Book1
	A	B	C
1	Communication/ Call Handling Skills;#Will (Behavioral Issues);#System Issue/ Machine Issues	Communication/ Call Handling Skills
2		#Will (Behavioral Issues)
3		#System Issue/ Machine Issues
Sheet2

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND(";",A1)-1)
B2	B2	=SUBSTITUTE(MID(SUBSTITUTE(";"&A1&REPT(" ",6),";",REPT(",",255)),2*255,255),",","")
B3	B3	=MID(A1, FIND(";", A1, FIND(";", A1)+1)+1,256)

Thank you Michael! Appreciate the help! Will try this one too!

 

[jayjavina]

You could also do this manually pretty quickly

- Select the column
- Text to Columns (on Data ribbon tab)
- Delimited - Next
- Set it like this

View attachment 32832

- Next & set first destination cell if required
- Finish


.. or the same as above but with vba
Assuming data in column A and results in B:D

Sub Split_Text()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).TextToColumns Range("B1"), xlDelimited, , True, False, True, False, False, True, "#"
End Sub

Thanks for the code Peter. I was also thinking of creating it in VBA.
I realized the data is much longer and may require a couple more columns to populate it. 5 columns to be exact.

Is it possible to have this code written for a "table" to easily populate in the results column after separating it?

 

[Peter_SSs]

Is it possible to have this code written for a "table" to easily populate in the results column after separating it?

Can you clarify?
- Is the original column in a table?
- Or you want the results in a table? (Do the results columns already exist?)
- Or both? (Same table/separate tables?)

Any chance you could give us a small dummy 'before' and an 'after' with XL2BB so we can be sure what we are dealing with and what you want and where everything is?

BTW, I suggest that you update your Account details (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’)