I guess we should wrap those formulas in an IF function call to properly
handle the case when the data cell is empty...

Prefix Cell
============
=IF(A2="","",LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9 },A2&"0123456789"))-1))

Suffix Cell
============
=IF(A2="","",RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 ))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your data starts in Row 2 (where Row 1 is assumed to be a header
row), and assuming your data is in Column A, put this formula in your
Prefix column...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789"))-1)

and put this formula in your Suffix column...

=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 )

and copy down as needed.

Rick


"Keith S" wrote in message
...
I have a spreadsheet where one column has cells with letters and numbers.
I
want to seperate the letters into one cell and the numbers into another
cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.