Hi Dave. That works a treat. Thankyou, you have saved me retyping over a
hundred references, and various other formulas.

"Dave Curtis" wrote:

Hi Ian,

try this: In A3, enter the following formula
=SUBSTITUTE(C3,RIGHT(C3,SUMPRODUCT((LEN(C3)-LEN(SUBSTITUTE(C3,{0,1,2,3,4,5,6,7,8,9},""))))),"" )

and in B3, enter

=--RIGHT(C3,SUMPRODUCT((LEN(C3)-LEN(SUBSTITUTE(C3,{0,1,2,3,4,5,6,7,8,9},"")))))

(note the double negative to coerce Excel to recognise the result as a
number.)

These should return only the leading letters and the trailing numbers.

Dave

"Iain" wrote:

Hi.

I have data in C3 as a reference to a name, either 2 or 3 letters followed
four numbers. I am trying to split this down over 2 cells with the letters
in A3 and the numbers in B3. The problem is i have two types of reference,
for example, hon2940 and po7951.

Can this be done? I know you can combine using "A3&B3" in C3, but i need to
go the other way. Thanks.