Find Partial Text and Return First Part of the Word

[dslhs]

Hi,

I'm looking for a formula that allows me to search for a specific partial text that is part of a word, and then return the characters that start off the word.

So for example, I have a cell with information like "English, Maths, Science, Community Skills, 45C/English, 3A/Maths, 5B/Science". I want to then have a cell for English that returns 45C (the first part of the word before "/English" in "45C/English), a cell for Maths that returns 3A (the first part of the word before "/Maths" in "3A/Maths), and a cell for Science that returns 5B.

Here is the minisheet:

Spreadsheet Test.xlsx
	A	B	C	D	E
1	Student	Courses/classes	English	Maths	Science
2	Student A	Community Skills, Community Skills: KS45, Community Skills: KS45: 45CD/Community Skills, Computing, Computing: IT + Computing, Computing: IT + Computing: Options 2/IT, EHCP Targets, EHCP Targets: KS4, English, Future Skills, EHCP Targets: KS4: KS4/EHCP2, English: KS45, Health Education, Future Skills: KS45, Life Skills, English: KS45: 45C/English, Health Education: KS45, Future Skills: KS45: 45C/Future Skills, Life Skills: Independent Living Skills, Life Skills: Independent Living Skills: Options 1/ILS, Health Education: KS45: 45C/Health Education, Life Skills: KS45, Life Skills: KS45: 45C/Life Skills, Mathematics, Mathematics: KS45, Mathematics: KS45: 45C/Maths, Physical Education, Physical Education: KS45, Physical Education: KS45: 45C/PE,
3	Student B	Community Skills, Community Skills: KS4, Community Skills: KS4: 4AB/Community Skills, EHCP Targets, EHCP Targets: KS4, English, Future Skills, English: KS4, Future Skills: KS4, English: KS4: 4A/English, Health Education, Future Skills: KS4: 4A/Future Skills, Health Education: KS4, Health Education: KS4: 4A/Health Education, Mathematics, Music, Physical Education, Music: Options 1/Music, Physical Education: KS4, Physical Education: KS4: 4A/PE, Mathematics: KS4, Mathematics: KS4: 4A/Maths,
4	Student C	Art, Art: KS3, Art: KS3: 3C/Art, Community Skills, Community Skills: KS3, Community Skills: KS3: 3CD/Community Skills, Computing, Drama, Computing: KS3, Drama: KS3, Drama: KS3: 3C/Drama, Computing: KS3: 3C/Computing, EHCP Targets, English, Food, English: KS3, Future Skills, English: KS3: 3C/English, Future Skills: KS3, Food: KS3, Future Skills: KS3: 3C/Future Skills, Food: KS3: 3C/Food, Horticulture, Horticulture: KS3, Horticulture: KS3: 3C/Horticulture, Humanities, Life Skills, Health Education, Humanities: KS3, Life Skills: KS3, Health Education: KS3, Life Skills: KS3: 3C/Life Skills, Humanities: KS3: 3C/Humanities, Health Education: KS3: 3C/Health Education, Music, Music: KS3, Music: KS3: 3C/Music, Physical Education, Physical Education: KS3, Registration, Mathematics, Physical Education: KS3: 3C/PE,
5	Student D	Community Skills, Community Skills: KS5, Community Skills: KS5: 5AB/Community Skills, EHCP Targets, EHCP Targets: KS5, EHCP Targets: KS5: KS5/EHCP1, Registration, Registration: KS5, Registration: KS5: Upstairs, Computing, Art, Computing: Options 1/Computing, English, Art: Options 2/Art, English: KS5, English: KS5: 5A/English, Future Skills, Future Skills: KS5, Health Education, Health Education: KS5, Future Skills: KS5: 5A/Future Skills, Health Education: KS5: 5A/Health Education, Mathematics, Mathematics: KS5, Mathematics: KS5: 5A/Maths, Physical Education, Physical Education: KS5, Physical Education: KS5: 5A/PE,
Sheet5

I tried: =MID(LEFT(B2,FIND("/Maths",B2)-1),FIND(" ",B2),LEN(B2)) but it doesn't quite work consistently

Thanks!

 

[kweaver]

Are there always 3 characters before the "/subject" or 2 with a leading blank?

Does this work?

=IFERROR(MID($B2,SEARCH("/"&C$1,$B2)-3,3),"")

 

[Peter_SSs]

Are there always 3 characters before the "/subject" or 2 with a leading blank?

Does this work?

=IFERROR(MID($B2,SEARCH("/"&C$1,$B2)-3,3),"")

I would add a TRIM so that a leading space is not returned with the shorter codes.

=IFERROR(TRIM(MID($B2,SEARCH("/"&C$1,$B2)-3,3)),"")

.. or in case the codes could be longer than 3 characters or shorter than 2 characters

22 07 07.xlsm
	B	C	D	E
1	Courses/classes	English	Maths	Science
2	Community Skills, Community Skills: KS45, Community Skills: KS45: 45CD/Community Skills, Computing, Computing: IT + Computing, Computing: IT + Computing: Options 2/IT, EHCP Targets, EHCP Targets: KS4, English, Future Skills, EHCP Targets: KS4: KS4/EHCP2, English: KS45, Health Education, Future Skills: KS45, Life Skills, English: KS45: 45C/English, Health Education: KS45, Future Skills: KS45: 45C/Future Skills, Life Skills: Independent Living Skills, Life Skills: Independent Living Skills: Options 1/ILS, Health Education: KS45: 45C/Health Education, Life Skills: KS45, Life Skills: KS45: 45C/Life Skills, Mathematics, Mathematics: KS45, Mathematics: KS45: 45C/Maths, Physical Education, Physical Education: KS45, Physical Education: KS45: 45C/PE,	45C	45C
3	Community Skills, Community Skills: KS4, Community Skills: KS4: 4AB/Community Skills, EHCP Targets, EHCP Targets: KS4, English, Future Skills, English: KS4, Future Skills: KS4, English: KS4: 4A/English, Health Education, Future Skills: KS4: 4A/Future Skills, Health Education: KS4, Health Education: KS4: 4A/Health Education, Mathematics, Music, Physical Education, Music: Options 1/Music, Physical Education: KS4, Physical Education: KS4: 4A/PE, Mathematics: KS4, Mathematics: KS4: 4A/Maths,	4A	4A
4	Art, Art: KS3, Art: KS3: 3C/Art, Community Skills, Community Skills: KS3, Community Skills: KS3: 3CD/Community Skills, Computing, Drama, Computing: KS3, Drama: KS3, Drama: KS3: 3C/Drama, Computing: KS3: 3C/Computing, EHCP Targets, English, Food, English: KS3, Future Skills, English: KS3: 3C/English, Future Skills: KS3, Food: KS3, Future Skills: KS3: 3C/Future Skills, Food: KS3: 3C/Food, Horticulture, Horticulture: KS3, Horticulture: KS3: 3C/Horticulture, Humanities, Life Skills, Health Education, Humanities: KS3, Life Skills: KS3, Health Education: KS3, Life Skills: KS3: 3C/Life Skills, Humanities: KS3: 3C/Humanities, Health Education: KS3: 3C/Health Education, Music, Music: KS3, Music: KS3: 3C/Music, Physical Education, Physical Education: KS3, Registration, Mathematics, Physical Education: KS3: 3C/PE,	3C
5	Community Skills, Community Skills: KS5, Community Skills: KS5: 5AB/Community Skills, EHCP Targets, EHCP Targets: KS5, EHCP Targets: KS5: KS5/EHCP1, Registration, Registration: KS5, Registration: KS5: Upstairs, Computing, Art, Computing: Options 1/Computing, English, Art: Options 2/Art, English: KS5, English: KS5: 5A/English, Future Skills, Future Skills: KS5, Health Education, Health Education: KS5, Future Skills: KS5: 5A/Future Skills, Health Education: KS5: 5A/Health Education, Mathematics, Mathematics: KS5, Mathematics: KS5: 5A/Maths, Physical Education, Physical Education: KS5, Physical Education: KS5: 5A/PE,	5A	5A
Search

Cell Formulas
Range	Formula
C2:E5	C2	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($B2,SEARCH("/"&C$1,$B2)-1)," ",REPT(" ",9)),9)),"")

This allows for codes up to 9 characters.
If there could be even more than that, then adjust the two "9's" near the end of the formula.

 

[dslhs]

Worked like a charm. Thank you so much! This board makes my life so much easier

 

[Peter_SSs]

Cheers!