FIND function help

[rs1aj]

I have model numbers that are either just seven digits or some are seven digits followed with a dash and up to 5 more characters. I have a vendor that will add three numbers to the front, then a dash, then my model number with sometimes putting a dash where is is suppose to be. Confused yet?

If you look at the attachment, I use the following formula.....=IF(LEN(TRIM(A2))=11,TRIM(MID(A2,FIND("-",A2)+1,7)),TRIM(MID(A2,FIND("-",A2)+1,7)&"-"&RIGHT(A2,LEN(A2)-11))) but it puts in 2 dashes in the place where they actually use my dash. I understand why it does it but I don't understand how to stop it. Is there a "FIND first occurrence" and do this, and skip the next occurrence?

Good luck and thanks for the help.

[AlKey]

Try this formula

Looks like I totally misread the requirements.

Corrected

=SUBSTITUTE(IF(ISERROR(--RIGHT(TRIM(A2))),REPLACE(TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2))),LEN(TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2)))),,"-"),TRIM(RIGHT(A2,LEN(A2)-FIND("-",A2)))),"--","-")

[ChemistB]

See if this works for all options

=REPLACE(SUBSTITUTE(TRIM(MID(A2,5,100)),"-",""),8,0,IF(LEN(SUBSTITUTE(TRIM(MID(A2,5,100)),"-",""))>7,"-",""))

[Jonmo1]

I believe this will give the results you're looking for.

=TRIM(REPLACE(SUBSTITUTE(REPLACE(A2,1,FIND("-",A2),""),"-",""),8,0,IF(LEN(TRIM(A2))>11,"-","")))

[rs1aj]

Not quite AlKey but close.

ChemistB and Jonmo1, both of these worked and thank you. I love this website.

[Ace_XL]

another way..

=MID(SUBSTITUTE(A2,"-","",2),5,7)&IF(LEN(TRIM(A2))>11,"-"&RIGHT(TRIM(A2)),"")

[Jonmo1]

=MID(SUBSTITUTE(A2,"-","",2),5,7)&IF(LEN(TRIM(A2))>11,"-"&RIGHT(TRIM(A2)),"")

That's awesome, but the substitute is actually completely unnecessary there...
Since the MID is retrieving 7 characters beginning at 5, the 2nd - doesn't appear till the 8th character beginning at 5..
So it's not even necessary to remove that 2nd -

Here it is without the substitute

=MID(A2,5,7)&IF(LEN(TRIM(A2))>11,"-"&RIGHT(TRIM(A2)),"")