Extract Certain Characters and Numbers

J

JavyD

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,
 
D

Duke Carey

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
 
R

Ron Rosenfeld

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,
Click to expand...


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
 
J

JavyD

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