Text Extraction (parsing)

[Curtis]

I have a column heading that contains year-month, department, and company
name (See Below)

2009-10 1151-Parts Dept - Casey Holdings

The first 7 characters represent the data, then there is a space, the next 4
characters represents the department (always 4 digits), the next characters
between the hyphen is the company group and the remaining text after the last
hyphen represents the company name.

I need a formula to extract the department department and one to extract all
the date, department, company group and company name (this is for future use)

Thanks

 

[Jacob Skaria]

'Date
=LEFT(A1,7)

'Department
=MID(A1,9,4)

'group
=TRIM(MID(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,255),"-",REPT(" ",255),2),6,255))

'name
=TRIM(RIGHT(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,255),"-",REPT(" ",255),2),255))

If this post helps click Yes

 

[Jacob Skaria]

You can use the below formula to get the date; which will return a date in
excel date format...
=DATE(LEFT(A1,4),MID(A1,6,2),1)

If this post helps click Yes

 

[Curtis]

THX