Any help would be greatly appreciated

[rslater]

To compile certain reports in work we export data into Excel. One of the Excel columns, normally column E will contain data as below:

Customer has error. LOG NUMBER: *Client: JA *Engineer: DW *Date of Work: 21-03-2016 *FPlace: FCH , Customer Contact: John Mileage: 30 Hours: 1 *DESCRIPTION OF WORK CALL ABOUT voltage problem TRAVEL FROM Base TO client ARRIVED ON SITE AT 08:00 AM WORK CARRIED OUT = CHECKED WARNINGS AND SHOWED JOHN THAT YOU MUST ENTER PASSWORD TO ALLOW YOU TO REMOVE WARNINGS ASKED JOHN TO KEEP AN EYE ON IT LEAVE SITE AT 09:00 AM Notes: NO PARTS USED

I need help on a way of checking a whole sheet, finding the word "Hours:" and then the value after it, which could be 1, 1.5, 11.5, etc. I would then like to copy the "Hours:" and the value to a new cell. I have tried FIND and MID but I am having no success. Any help would be greatly appreciated.

 

[smallxyz]

=MID(A1,FIND("Hours: ",A1)+LEN("Hours: "),FIND(" *DESCRIPTION",A1)-FIND("Hours: ",A1)-LEN("Hours: "))

 

[thisoldman]

smallxyz's solution returns the digits only portion of "Hours: nn". The following returns the whole "Hours: nn" phrase.

=MID(A2,FIND("Hours:",A2),FIND(" ",A2,FIND("Hours:",A2)+7)-FIND("Hours:",A2))

My solution requires the hours digits to be followed by a space; smallxyz's formula requires the hours digits to be followed by the string " *DESCRIPTION". Both fail if the string "Hours: nn" is the terminating text in the cell.

 

[L. Howard]

Assuming that data is in a single cell, and the hours don't exceed 5 characters, try this

1
1.1
11.1
1.11
11.11
2

=MID(B1,FIND("Hours:",B1),11)

Which returns this.

Hours: 1 *D
Hours: 1.1
Hours: 11.1
Hours: 1.11
Hours: 11.1
Hours: 2 *D

With a Text To Columns > * as Delimiter you get

Hours: 1 
Hours: 1.1 
Hours: 11.1
Hours: 1.11
Hours: 11.1
Hours: 2

Howard

 

[rslater]

Many thanks gentlemen, the solutions worked. You have saved me a lot of frustration and some hair!

All the best, RSlater.