Unfortunately that doesn't help. Maybe if you posted a larger sample of data. And enter the expected results from the formula you're looking for in the last column.
Below is the data set again - this time I have added the expected result in the last column - the first 616 rows of data up to EHLH-2337 have the test report # hard-coded, (instead of by your formula) so the formula now needs to start the counting and auto-increment from EHLH-2338 (this effectively becoming the "1st" number in the hose sequence, regardless of the row number) - hope this helps and makes sense this time?
[TABLE="width: 413"]
<TBODY>[TR]
[TD]Certification Type</SPAN>[/TD]
[TD]Test Type Code</SPAN>[/TD]
[TD]Test Report #</SPAN>[/TD]
[TD]Expected result</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[TD]EHLH-2335</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[TD]EHLH-2336</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[TD]EHLH-2337</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0617</SPAN>[/TD]
[TD]EHLH-2338</SPAN>[/TD]
[/TR]
[TR]
[TD]Hose</SPAN>[/TD]
[TD]EHLH</SPAN>[/TD]
[TD]EHLH-0618</SPAN>[/TD]
[TD]EHLH-2339</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[TD]EHLMF-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[TD]EHLMF-0002</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[TD]EHLMF-0003</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[TD]EHLMF-0004</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[TD]EHLMF-0005</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[TD]EHLMF-0006</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[TD]EHLMF-0007</SPAN>[/TD]
[/TR]
[TR]
[TD]Metal Fitting</SPAN>[/TD]
[TD]EHLMF</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[TD]EHLMF-0008</SPAN>[/TD]
[/TR]
[TR]
[TD]Safety Valve</SPAN>[/TD]
[TD]EHLSV</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[TD]EHLSV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[TD]EHLV-0001</SPAN>[/TD]
[/TR]
[TR]
[TD]Valve</SPAN>[/TD]
[TD]EHLV</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[TD]EHLV-0002</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]