View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Frank Kabel Frank Kabel is offline
external usenet poster
 
Posts: 3,885
Default Split numbers and text from a cell

Hi
try if the numeric part is always at the end:
B1: Text part
=LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1)

C1: numeric part:
= --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20)

where seq is a defined name with the formula
=ROW(INDIRECT("1:1024"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
Hi,

I have a column with cells containing codes formatted:

XXXXnnnnnn (where X=letter and n= number)
the number of letters is variable.

I want to split the cells in one column with text and one clomn with
numbers.
Can anyone help me?