Problem separating a long string into four separate fields?

Hi

I have a little training, from a course I did some time ago, with functions such as LEFT, MID, RIGHT, FIND, IF etc. Nesting I need some practice with but am sure I will get there.

I have my first task in the real world and it is to separate a string into four separate fields (please see image at the bottom).

An example of the string, one of several hundred of them, is COMP10000 Introduction to Computing 2015-16 2nd Semester.

My challenge is to separate it into four fields:
Course code: COMP10000
Year: 2015-16
Course title: Introduction to Computing
Semester: 2nd Semester

The first step to separate the course code was easy and I used the formula =LEFT(G2,FIND(" ",G2)-1) where the full string is in cell G2. I then fill down for several hundred records.

I hope kind folk can hold my hand through the next three fields?

YEAR
The year is in the middle of the string but even if I used MID cannot use FIND to locate the space as there are a number of them?

SEMESTER
My first instinct is to use RIGHT and FIND but there are two spaces not one?
Also in some cases there is a blank with the relevant semester or full year missing (where I would probably treat it as full year).

COURSE TITLE
Probably a combination of MID and FIND and another function?

I would very much appreciate a steer on how best to approach these remaining 3 fields?

Many thanks!

capture20160613162709489.png

Hi,

One way
Year:

Formula:

=MID(G1,FIND("-",G1)-4,7)

Semester:

Formula:

=RIGHT(G1,LEN(G1)-FIND("-",G1)-3)

Course:

Formula:

=SUBSTITUTE(LEFT(G1,FIND("-",G1)-6),N1&" ","")

Usual caveat with all this string slicing stuff. The text strings must be consistent. e.g. the year must always be yyyy-yy not say yyyy-yyyy, or yyyy:yy

...did you try the formulae I gave you in post #3?

Obviously change the G1 reference to G2 & N1 to N2 where necessary.

Year and Semester works a treat thank you Richard. Course title adds the course code at the beginning though, for example 'COMP10000 Introduction to Computing' only needs to be 'Introduction to Computing'. I expect it just needs a tweak. I will use the formulas to get the real job done then return when I can to work out exactly how the formulas were put together. You guys inspire me to learn not just ask! I note another reply below so will take a look too, I think there can often be more than one way to skin a cat.

Originally Posted by colgor

Year and Semester works a treat thank you Richard. Course title adds the course code at the beginning though, for example 'COMP10000 Introduction to Computing' only needs to be 'Introduction to Computing'. I expect it just needs a tweak. I will use the formulas to get the real job done then return when I can to work out exactly how the formulas were put together. You guys inspire me to learn not just ask! I note another reply below so will take a look too, I think there can often be more than one way to skin a cat.

Hi,

I don't see that - see attached.

Originally Posted by colgor

OK, Richard, I did it again and your course title formula worked so I must have typed or pasted it in wrong somehow the first time I tried it.

I am attaching a small sample of real data where there are still problems though. I have taken many of the correct rows out so there appears to be more incorrect than there actually are. I hope it is enough to see where the problem might be?

Thanks again!

Variations
O2:

Formula:

=MID(G2,IF(LEN(G2)-LEN(SUBSTITUTE(G2,"-",""))=1,FIND("-",G2)-4,FIND("^",SUBSTITUTE(G2,"-","^",2))-4),7)

P2:

Formula:

=RIGHT(G2,IF(LEN(G2)-LEN(SUBSTITUTE(G2,"-",""))=1,LEN(G2)-FIND("-",G2)-3,LEN(G2)-FIND("-",G2)-3))

Q2:

Formula:

=SUBSTITUTE(LEFT(G2,IF(LEN(G2)-LEN(SUBSTITUTE(G2,"-",""))=1,FIND("-",G2)-6,FIND("^",SUBSTITUTE(G2,"-","^",2))-6)),N2&" ","")

Enter formula in cell N2 and pull formula across and then down

Formula:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE($G2,FIND("-",$G2)-5,1,"|"),FIND("-",$G2)+3,1,"|")," ","|",1),"|",REPT(" ",99)),99*(COLUMNS($G:G)-1)+1,99))

v	G	H	I	J	K	L	M	N	O	P	Q
1	COURSE_NAME							Course code	Course Title	Year	Semester
2	COMP10000 Introduction to Computing 2015-16 2nd Semester							COMP10000	Introduction to Computing	2015-16	2nd Semester
3	PHYS10100 Introduction to Physics 2015-16 Full Year							PHYS10100	Introduction to Physics	2015-16	Full Year
4	CHEM10111 Introductory Chemistry 2015-16							CHEM10111	Introductory Chemistry	2015-16
5	GEOG10754 Introduction to Geography 2015-16 1st Semester							GEOG10754	Introduction to Geography	2015-16	1st Semester
6	MATH21192 Maths 2015-16 2nd Semester							MATH21192	Maths	2015-16	2nd Semester
7	HIST21202 History 2015-16 2nd Semester							HIST21202	History	2015-16	2nd Semester
8	SCIE21211 Science 2015-16 1st Semester							SCIE21211	Science	2015-16	1st Semester
9	LITE21221 Literature 2015-16 1st Semester							LITE21221	Literature	2015-16	1st Semester
10	LATI21232 Latin 2015-16 2nd Semester							LATI21232	Latin	2015-16	2nd Semester
11	INFO21242 IT 2015-16 2nd Semester							INFO21242	IT	2015-16	2nd Semester
12	COSC21252 Computer Science 2015-16 2nd Semester							COSC21252	Computer Science	2015-16	2nd Semester
13	PHED21261 Physical Education 2015-16 1st Semester							PHED21261	Physical Education	2015-16	1st Semester
14	DRAM21272 Drama 2015-16 2nd Semester							DRAM21272	Drama	2015-16	2nd Semester

Originally Posted by colgor

SNAG!

...There was a caveat in my post #3.

Often we find with large data sets that what might work for a small subset does not with the whole population since there may be subtle differences which affect string slicing stuff.

Stating the obvious I suppose, but can't the system which spews out the single string of data include field separator characters?

If you have encountered snags then we'll need to see examples. Densitise the data if necessary but make sure it still 'snags' so that we have something to work with and correct

Please see attached file with improved formula that should work fine with your data.

Formula:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(LEFT($G2,SEARCH(" ????-?? ",$G2))&"|"," ","|",1)&SUBSTITUTE(MID($G2,SEARCH(" ????-?? ",$G2)+1,25)," ","|",1),"|",REPT(" ",99)),99*(COLUMNS($G:G)-1)+1,99))

Many thanks Alkey. Works almost perfectly with the real data. There are though just a few records that still don't work. It is where the string and particularly the course title are too long so spill into the other cells. The maximum characters of a course title including spaces appears to be 126 and the maximum string length including spaces appears to be 161. Can that help you to to polish it off?

As an aside, this exercise has proved that the data analysis course I did has taught me what can be done but the complex formulae, unless I am working regularly with complex datasets, might be impractical for me to take time to learn as so much practice would be required? But once the real world problem is solved (nearly!) then I can still learn on YouTube about TRIM SUBSTITUTE SEARCH etc and help me get how you did it and what is possible in future, but perhaps only with the help of your good selves on this forum? It might be best to simply ask for some code as required as in this case though of course I will learn what is doable?