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.

to get the letters at the beginning of entry in C3:
=LEFT(C3,LEN(C3)-4)
to get the numbers from it
=RIGHT(C3,4)

Hope that helps you out.

"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.

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.

Hi, and thanks for that. That works when there are 3 letters in the
reference, but not when there are 2. Is there a way of differentiating
between this, and automatically altering the "-4" to "-3"?

"JLatham" wrote:

to get the letters at the beginning of entry in C3:
=LEFT(C3,LEN(C3)-4)
to get the numbers from it
=RIGHT(C3,4)

Hope that helps you out.

"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.

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.

On Tue, 3 Feb 2009 05:01:01 -0800, 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.

A3: (letters)
=LEFT(C3,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},C3&"1234 567890"))-1)

B3: (numbers)
=MID(C3,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},C3&"12345 67890")),99)


--ron

Works both ways for me. The "4" refers to the number of numeric digits at
the end of the entry in C3. You said 2 or 3 letters (always) followed by 4
digits. Would have been faster than the other solution here, but it would
appear that you're going to need the longer formula for your particular needs.

"Iain" wrote:

Hi, and thanks for that. That works when there are 3 letters in the
reference, but not when there are 2. Is there a way of differentiating
between this, and automatically altering the "-4" to "-3"?

"JLatham" wrote:

to get the letters at the beginning of entry in C3:
=LEFT(C3,LEN(C3)-4)
to get the numbers from it
=RIGHT(C3,4)

Hope that helps you out.

"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.