Using len and left functions together

[CRISTINA1128]

I have the following data:
15ab
12cc
6ll
9bc

and i need to extract the number portion of the string. My text string will be either 3 or 4 characters long.
If len=4, then left,2. If len=3, then left,1.

I can't make the formula work in excel. Can someone please help?

 

[dreid1011]

Hrm, perhaps this:

=IF(LEN(A2)=4,LEFT(A2,2),LEFT(A2,1))

This is assuming that your len is ONLY 3 or 4 as you stated. If it is more or less, then the formula will need adjustment.

 

[philwojo]

To do what you want you can try this, and I'm sure there are other ways to do this as well.

=IF(LEN(A1)=4,LEFT(A1,2),LEFT(A1,1))

 

[njimack]

2 options spring to mind:
=SUBSTITUTE(A1,RIGHT(A1,2),"")
=LEFT(A1,LEN(A1)-2)

 

[Special-K99]

Or simply

=LEFT(A1,LEN(A1)-2)

 

[CRISTINA1128]

This works!! Thank you!!! )

 

[CRISTINA1128]

Last question - this text string is in a pivot table and i was trying to insert a calculated field. I was replacing "a1" with the column header, which in this case is "casepack/eaches size". However, the result i'm getting is "# of packs" on 1 line and "sum of casepack" on the line below it....
Any advise?