Text Extraction (parsing)
 

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

Text Extraction (parsing)
 

'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


"Curtis" wrote:

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

Text Extraction (parsing)
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

'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


"Curtis" wrote:

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

Text Extraction (parsing)
 

THX

"Curtis" wrote:

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