Text Extraction (parsing)

  • Thread starter Thread starter Curtis
  • Start date Start date
C

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
 
'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
 
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
 
Back
Top