Need help with a formula please.

[MikeyZ]

Here are a few of the lines of data.
Each is in one cell in column H on a line.

420BOZ(), 629PZA(), 629GED(), 629ZFA(), 630ZQA(), 630PFA()
430ZZA(), 521ZZA(), 629PZA(), 629GED(), 629ZZA(), 630PEA()
430ZZA(), 521ZZA(), 629PZA(), 629GED(), 629ZZA(), 630ZQA()
532ZZA(), 850JGA(), 850NAA(), 265DEB(), 860SRA(), 860RCA()

In an adjacent cell all I want to do is pull the first 6 digits of the first number that starts with 630.
If there isn't any I want a blank cell.
Can anyone come up with a working formula, I'm stuck.

 

[Max1616]

You can use this:
=IFERROR(MID(H1,SEARCH(630,H1),6),"")

Sincerely,
Max

 

[MikeyZ]

Thank you.. Works great..

 

[pjmorris]

Hi Mikey,

An alternative solution would be to use this: =IF(LEFT(H1,3)="630",LEFT(H1,6),"")

Both will work with the data you've provided, but Max's solution will also return '630AA(' if the source data were something like: 8630AA() ie the 630 is within the string rather than at its start.

Just a thought.

Regards

Peter