"uncombine" data in a cell

[Iain]

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.

 

[JLatham]

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]

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

 

[Iain]

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

 

[Ron Rosenfeld]

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&"1234567890"))-1)

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


--ron

 

[JLatham]

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.