Extract Certain Characters and Numbers
 

Calling all Geniuses,

Is it possible to trim down something like this, I have over 4 thousand of
these, they are SKU's with case pack size

9811HAC-6, I need it to be just 9811, I need it to strip the HAC-6
5465PAD-12, I need it to be just 5465, I need it to strip the PAD-12
and so on.

Regards,

Extract Certain Characters and Numbers
 

If it is always the first 4 characters, use this formula to return a text
representation of the #s.

=left(A1,4)

If you want Excel to view the results as numbers, use

=1*left(A1,4)

This assumes your SKU is in A1

"JavyD" wrote:

Calling all Geniuses,

Is it possible to trim down something like this, I have over 4 thousand of
these, they are SKU's with case pack size

9811HAC-6, I need it to be just 9811, I need it to strip the HAC-6
5465PAD-12, I need it to be just 5465, I need it to strip the PAD-12
and so on.

Regards,

Extract Certain Characters and Numbers
 

On Tue, 28 Mar 2006 06:19:47 -0800, JavyD
wrote:

Calling all Geniuses,

Is it possible to trim down something like this, I have over 4 thousand of
these, they are SKU's with case pack size

9811HAC-6, I need it to be just 9811, I need it to strip the HAC-6
5465PAD-12, I need it to be just 5465, I need it to strip the PAD-12
and so on.

Regards,


If it is always the same number of digits:

=LEFT(A1,4)

If the number of digits may vary:


=LEFT(A1,MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:8")),1)),FALSE)-1)

entered as an **array** formula. After copying the formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.

Note the number "8" in the formula above. This should be changed to reflect
the maximum number of digits that might be present in the SKU.


--ron

Extract Certain Characters and Numbers
 

Thanks Duke, but it's not always the first characters, it could be something
like 981165PPP-6, then I'll need 981165.

"Duke Carey" wrote:

If it is always the first 4 characters, use this formula to return a text
representation of the #s.

=left(A1,4)

If you want Excel to view the results as numbers, use

=1*left(A1,4)

This assumes your SKU is in A1

"JavyD" wrote:

Calling all Geniuses,

Is it possible to trim down something like this, I have over 4 thousand of
these, they are SKU's with case pack size

9811HAC-6, I need it to be just 9811, I need it to strip the HAC-6
5465PAD-12, I need it to be just 5465, I need it to strip the PAD-12
and so on.

Regards,