Extract details form code with formula

**freud1** · 07-13-2012, 05:48 AM

Hi Guys,

I have a series of codes in column A which follow the following format:

336 Course Title 2012 Classroom 2012/09/07 09:30 - 12:30 Course Location

<3 digit number> <Course Title> <Year> <Classroom> <Date in format yyyy/mm/dd> <Start time - End Time> <Course Location>

I need a method to identify certain details in the adjacent columns.

I need to extract:

Course title
Course location

I cannot use text to columns so I need to do it through formulae and I cannot even begin to think how.

Help me Excel Gurus!

Thank you

F

**Pete_UK** · 07-13-2012, 05:58 AM

Please post two or three actual examples of the entries in column A (or attach a sample workbook) - should be easy enough.

Pete

**freud1** · 07-13-2012, 06:10 AM

Hi Pete,

Some sample values:

336 Science Course 2012 Classroom 2012/10/05 09:30 - 12:30 London
336 Excel Course in House 2012/08/02 09:00 - 11:00 RM2
336 French 2012 Classroom 2012/09/18 09:30 - 16:30 South East
336 Historical Artifacts 2012 Classroom 2012/08/07 09:30 - 12:30 Made Up road
336 Cooking workshop 2 Day 2012/10/04-05 09:00 - 17:00 The new building
364 Basic common sense 2012 Classroom Induction 2012/09/20 09:00 - 17:00 Lolcation 2
364 Basic understanding + application 2012 Classroom 2012/07/26 09:30-12:30 Lodge

**Pete_UK** · 07-13-2012, 06:45 AM

Assuming your data starts in A2, put this formula in B2 to get the course title:

=MID(A2,5,SEARCH(" 2012",A2)-4)

and put this in C2 to get the location:

=MID(A2,SEARCH(":",A2)+12,255)

then you can copy these down to the bottom of your data.

Please note that your final example does not have spaces either side of the hyphen, and so it returns "dge" instead of "Lodge" - I assumed this was a typo on your part.

Hope this helps.

Pete