Seperating text into 3 columns

[Colleen45]

I have a list of texts of varying lengths, always seperated by the "first space" and then by a semi-colon

Sample
CTL3-Harare_bejnf [=Book - English juvenile non fiction];2417
CTL3-Harare_bejt [=Book - English juvenile Teens];155
CTL3-Harare_belp [=Book - English fiction large print];764


I'm battling to create formulas that will put this in 3 Columns, seperated by the "first space" and then by the semi-colon - The "first space" and the sem-colon must be omited from the results.

 

[noman]

Couldn't you just use text to columns?

 

[VoG]

Perhaps

Excel Workbook
	A	B	C	D
1	CTL3-Harare_bejnf [=Book - English juvenile non fiction];2417	CTL3-Harare_bejnf	[=Book - English juvenile non fiction];	2417
2	CTL3-Harare_bejt [=Book - English juvenile Teens];155	CTL3-Harare_bejt	[=Book - English juvenile Teens];	155
3	CTL3-Harare_belp [=Book - English fiction large print];764	CTL3-Harare_belp	[=Book - English fiction large print];	764
Sheet1

 

[Colleen45]

This does not work as there is more than one space,

 

[VoG]

This does not work as there is more than one space,

How do you want it split then?

Omitting semi colon

Excel Workbook
	A	B	C	D
1	CTL3-Harare_bejnf [=Book - English juvenile non fiction];2417	CTL3-Harare_bejnf	[=Book - English juvenile non fiction]	2417
2	CTL3-Harare_bejt [=Book - English juvenile Teens];155	CTL3-Harare_bejt	[=Book - English juvenile Teens]	155
3	CTL3-Harare_belp [=Book - English fiction large print];764	CTL3-Harare_belp	[=Book - English fiction large print]	764
Sheet1

 

[Colleen45]

Thanks Peter, however the second column is keeping the "First Space" and the semi-colon, how would we tweak this so that it is removed (in the same formula)

 

[Colleen45]

looks like there are cross posts

 

[VoG]

In my results there are no leading or trailing spaces in any of the three split columns.

 

[anandvarma]

try this

Excel Workbook
	A	B	C	D
1	CTL3-Harare_bejnf [=Book - English juvenile non fiction];2417	CTL3-Harare_bejnf	[=Book - English juvenile non fiction]	2417
2	CTL3-Harare_bejt [=Book - English juvenile Teens];155	CTL3-Harare_bejt	[=Book - English juvenile Teens]	155
3	CTL3-Harare_belp [=Book - English fiction large print];764	CTL3-Harare_belp	[=Book - English fiction large print]	764
Sheet1

 

[Colleen45]

My second columnn is definately showing a leading space, everything else works perfectly, thank you
Can I adjust something or do something that will remove the leading space?