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?

Original Code	First 5	Trailing Character
99999	99999
99999-25	99999	25
99999R	99999	R

<tbody>
</tbody>

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)="-"),"")