Parsing serial numbers

[Sean15]

Hi:

I have a set of long serial numbers formatted as shown below. I want to return ALL text and numbers after 991-

Example:

600-1150301-520-199-991-CPG1500--

Required value would CPG1500--

Could you help please?

Sean

 

[mumps]

There may be a more efficient formula but this one seems to work: =MID(MID(A1,FIND("991",A1,1),99999),5,99999)
Assumes serial number is in A1.

 

[Fluff]

Another option
=MID(A1,SEARCH("991-",A1)+4,999)

 

[Rick Rothstein]

And one more option...

=REPLACE(A1,1,FIND("-991-",A1)+4,"")

 

[Rick Rothstein]

And one more option...

=REPLACE(A1,1,FIND("-991-",A1)+4,"")

And if it is at all possible for -991- to appear more than once in the serial number, then use this instead...

=TRIM(RIGHT(SUBSTITUTE(A7,"-991-",REPT(" ",99)),99))

 

[Sean15]

Thank you very much for your help.


Sean