"uncombine" data in a cell
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.
|