Excel 2010, needing help Separating lines of characters into columns = #'s and letters

[the_clay_bird]

What im needing to accomplish with this task is taking a series of alphanumeric text and either separate them into 2 columns, one of numbers and one of letters.

For example:

0072545SN
0081558SN
0081986SN
0082014SN

I need to get this into columns like:
0072545 SN
0081558 SN
0081986 SN
0082014 SN

OR alternatively, my end game is I need to get The letters in front of the numbers a la a prefix rather than a suffix.

Any ideas on how to do this as painlessly as possible?

[AlexandraT]

If your combination of numbers and text is always the same, 7 numbers and 2 letters, use this:

=RIGHT(A1,2)&LEFT(A1,7)

and apply to the entire column.

Alex

[the_clay_bird]

Hmm. That is helpful for some but not all. I should have included a greater number of examples. here's a more all encompassing sample:

0072545SN
0081558SN
0081986SN
0082014SN
011238-002UR
011709-004UR
012798-000U
012798-000UR
012798-001UR
012979-000UR
0140011JL
0140011U
0140030JL
0140030U
015772-001UR
015941-001U
015941-001UR
015941-002UR
0180846SN
0181847JL
0252532JL
0253100SJL
0253100SU
0253100U

[AlexandraT]

Try this:

=IF(ISERROR(MID(RIGHT(A2,2),1,1)+1),RIGHT(A2,2)&LEFT(A2,LEN(A2)-2),RIGHT(A2,1)&LEFT(A2,LEN(A2)-1))

This is still dependent on the combination having 1 or 2 letters at the end and not more.

Alex

[berlan]

Hi, see if the attached helps.

Kind regards,
berlan

[the_clay_bird]

Hi, see if the attached helps.

Kind regards,
berlan

I was able to use these formulas combined with the text to columns command and then a simple "A2&B2" combo to get exactly what I need

Thanks everybody for the help!

[newdoverman]

I cobbled this together and it might work for you.

=MID(A1,MIN(FIND({"-",0,1,2,3,4,5,6,7,8,9},A1&"0123456789-")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"-",0,1,2,3,4,5,6,7,8,9},""))))&" "&SUBSTITUTE(A1,MID(A1,MIN(FIND({"-",0,1,2,3,4,5,6,7,8,9},A1&"0123456789-")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"-",0,1,2,3,4,5,6,7,8,9},"")))),"")

[FlameRetired]

Another possibility?

=RIGHT($A1,LEN($A1)-LOOKUP(2,1/ISNUMBER(--MID($A1,ROW($1:$30),1)),ROW($1:$30)))&LEFT($A1,LOOKUP(2,1/ISNUMBER(--MID($A1,ROW($1:$30),1)),ROW($1:$30)))