Find and extract a value from a text field?

[smithgt]

I'm looking to extract a value from a lengthly test field.

The wording and field length can vary, but there are a couple of constants.

An example oif the cell K2 & K3 would read

<TABLE style="WIDTH: 644pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=859><COLGROUP><COL style="WIDTH: 644pt; mso-width-source: userset; mso-width-alt: 31414" width=859><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 644pt; HEIGHT: 10.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=14 width=859>House Roy: Archiving Subright Royalty Statements (Split from V52157) - ESTIMATE: 7.00 Hours (7 Hours) AUTHORISED </TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 644pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=859><COLGROUP><COL style="WIDTH: 644pt; mso-width-source: userset; mso-width-alt: 31414" width=859><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 644pt; HEIGHT: 10.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=14 width=859>House Roy: Develop Engine to allow formula level reporting - ESTIMATE: 10.00 Hours (1 Days, 2.5 Hours) AUTHORISED </TD></TR></TBODY></TABLE>

I want the hours value. It's always after the word "ESTIMATE:" and ends in "Hours". However the value could be 0.50, or 18.00 so does vary in length.

Can you help?

 

[kpark91]

Not the BEST way to extract (I am sure) but try

=TRIM(MID(A1,FIND("ESTIMATE:",A1)+LEN("ESTIMATE:"),FIND("Hours",A1,FIND("ESTIMATE:",A1))-FIND("ESTIMATE:",A1) - LEN("ESTIMATE:")))

A1 is where your string is.
So, just replace A1 with K2 or w.e

 

[smithgt]

Thanks - that works well.

Just one thing, if the text doesn't match then the cell contains a #Value error.

Is to possible to replace this with a zero value?

thanks
​

 

[kpark91]

=IFERROR(TRIM(MID(A1,FIND("ESTIMATE:",A1)+LEN("ESTIMATE:"),FIND("Hours",A1,FIND("ESTIMATE:",A1))-FIND("ESTIMATE:",A1) - LEN("ESTIMATE:"))), 0)

 

[smithgt]

Solved with =IFERROR