Formula for Separating Digits after nth Digit

[Excel1991]

Hi all,

I have a list of codes which can be of varying lengths ("99999" or "99999-25" or "99999R") and I would like to take the put the first 5 digits in one column and whatever comes after the first 5 digits in the net column?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Original Code[/TD]
[TD]First 5[/TD]
[TD]Trailing Character[/TD]
[/TR]
[TR]
[TD]99999[/TD]
[TD]99999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]99999-25[/TD]
[TD]99999[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]99999R[/TD]
[TD]99999[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]

I dont think text to columns would work because i do not have a common deliminator. Does anyone know of a formula that can be used in this instance? Thank you

 

[Rick Rothstein]

And just to prove there is almost always more than one way to do things in Excel, here are three more formula that will return those trailing characters (if any)...

=MID(A2,6+(MID(A2,6,1)="-"),99)

=MID(A2,6+COUNTIF(A2,"*-*"),99)

=REPLACE(A2,1,5+(MID(A2,6,1)="-"),"")