Problem separating a long string into four separate fields?

**colgor** · 06-13-2016, 11:33 AM

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

**JohnTopley** · 06-13-2016, 11:44 AM

Please post a file (not image) with a representative sample of your strings.

To upload a file click "Go Advanced" then scroll down to "Manage Attachments"

**Richard Buttrey** · 06-13-2016, 11:55 AM

Hi,

One way
Year:

Formula:

Please Login or Register  to view this content.

Semester:

Formula:

Please Login or Register  to view this content.

Course:

Formula:

Please Login or Register  to view this content.

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

**colgor** · 06-13-2016, 01:29 PM

Thanks John. I've attached an Excel 2013 file with a little more sample data as requested. Though there are several hundred records in the real data I think the sample I have given should suffice, if not please let me know? Thanks again.

**colgor** · 06-13-2016, 01:33 PM

PS. Re. 'CHEM10111 Introductory Chemistry 2015-16 ', in row 3, there are records like that in the real dataset where there is missing information for the semester field. Only one example in my sample but it is not unique.

**Richard Buttrey** · 06-13-2016, 01:33 PM

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

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

**colgor** · 06-13-2016, 01:35 PM

Richard, sorry I should have read all posts first, rather than work through one at a time. Lesson learnt and will test your formulas now and feedback very shortly. Thanks you! :-)

**AlKey** · 06-13-2016, 02:16 PM

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

Formula:

Please Login or Register  to view this content.

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

**JohnTopley** · 06-13-2016, 02:19 PM

Richard, they are working OK!!!

**colgor** · 06-13-2016, 02:37 PM

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.

**colgor** · 06-13-2016, 02:56 PM

That is very clever Alkey thank you, it works great. So way above my head that I might be tempted to use Richard's solution, once he comes back to me on tweaking the course title field. That way, I might get a way with bluffing my way and pretending to my boss that I actually did the code! :-) Seriously, I will test both now with the real data. I have a real heavy schedule coming up over summer but I will no doubt return to this thread (when no longer necessary simply for the task in hand) and ask questions, perhaps, once I've youtubed the functions that are new to me and tried to follow your formulas. I love the way Excel is so powerful and am keen to learn.

**colgor** · 06-13-2016, 02:58 PM

Thanks for starting the ball rolling John. Lots of really helpful folk on here including you, thanks again!

**JohnTopley** · 06-13-2016, 03:11 PM

With Richards's formula I did not get the course code added to the course title. Check the formula again.

**colgor** · 06-13-2016, 03:15 PM

SNAG!

In both cases, Richard and Alkey, when transferring the code to several hundred records the long course titles sometimes get split and overwritten into the Year and Semester fields. It might be that I need to let you know the maximum length of the string or course titles? I have no problem sharing the actual data with one or both if that helps? There is no personal data but it belongs to the organisation I work for so would rather not publish it wider than that? Non Excel things now like getting fed and saying a few words to family so will login in later this evening or tomorrow now to look for your replies. Thanks again!!!

**JohnTopley** · 06-13-2016, 03:24 PM

Post a file with samples of the "snags" !!!!

**Richard Buttrey** · 06-13-2016, 03:37 PM

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.

**Richard Buttrey** · 06-13-2016, 03:44 PM

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

**colgor** · 06-13-2016, 04:20 PM

I noted your caveat Richard, thanks, but I hoped it would not be an issue as when skimming all seemed OK.

Indeed, going back to the data provider in my organisation was my first thought, but I saw it as an opportunity to practice, learn more and apply some of my learning. If we hit a brick wall I will do certainly do that.

**colgor** · 06-14-2016, 02:33 PM

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!

**AlKey** · 06-14-2016, 02:44 PM

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

Formula:

Please Login or Register  to view this content.

**Richard Buttrey** · 06-14-2016, 04:58 PM

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:

Please Login or Register  to view this content.

P2:

Formula:

Please Login or Register  to view this content.

Q2:

Formula:

Please Login or Register  to view this content.

**colgor** · 06-15-2016, 07:31 AM

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?

**colgor** · 06-15-2016, 07:37 AM

I'm afraid the new variations gave similar results Richard. Alkey has almost got me there as you will see from the other post. In the short term Alkey's approach should get the job done. However, if you are happy and have time, I'm still happy to get your alternative way to work. My second paragraph in post 22 applies equally to the work I have beendoing wiht you. Your codes are broken down and might be easier to follow when trying to work out how you did it? I might then step up and look at Alkey's one code solution?

**Richard Buttrey** · 06-15-2016, 11:26 AM

Originally Posted by colgor

I'm afraid the new variations gave similar results Richard.

Was that with the test data you originally sent or new data which may have different formats?

Clearly you have a working formula from Allkey which is great. Just for completion this is the workbook with the variation formulae in.

**colgor** · 06-15-2016, 03:04 PM

Yes, thanks Richard, I tested it on real data. The email you just attached is skewed with data across the fields but no matter.
Should AlKey tweak that last piece of code my real life problem should be solved. Thanks again to you both.

Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Re: Problem separating a long string into four separate fields?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to separate names in a long cell string

Bookmarks

Bookmarks

Posting Permissions