Thanks
Michael
=LEFT(A1,MIN(IF(1-ISERROR(FIND({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1)))-1)&
RIGHT(A1,LEN(A1)-MAX(IF(1-ISERROR(FIND
({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1))))
Gerry
>.
>
which expects a series of digits followed by a series of non-digits.
"Michael" <kni...@lineone.net> wrote in message
news:00d301c351f9$fcd3c770$7d02...@phx.gbl...
Assuming the numbers come first and then come the letters, the following
*array-entered* formulas will do that. The string is assumed to be in cell A4.
For the numbers:
=LEFT(A4,MATCH(TRUE,ISERROR(-MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)),0)-1)
For the letters:
=MID(A4,MATCH(TRUE,ISERROR(-MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)),0),255)
To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.
--ron