Excel 2007 : Need to separate a description field in various character lines

[jdaggie6]

I am new here and kind of an excel noobie, but I need help splitting up a description that is way to long and returning the segments to a column. I tried to use the =mid() formula to count out, but I need each new cell in the column to reference the original part number with the segmented description. The load sheet can only take up to 72 characters, but can have duplicate materials. Here is an example:

I want it to go from this(Material is A1 and Description is B1) to:

MATERIAL# DESCRIPTION

10312738 DISC, SANDING-12" DIAMETER, 80 GRIT, NO, HOLE, TYPE 248D, PRESSURE SENSTIVE, ADHESIVE BACKING, 3M, 25 PER BOX, PEOPLE HAVE BEEN COMPLAINING ABOUT, NORTON BRAND NOT STICKING GOOD ENOUGH., BUY 3M BRAND

I need to have multiple lines but each description is limited 72 characters,

MATERIAL # DESCRIPTION
10312738 DISC, SANDING-12" DIAMETER, 80 GRIT, NO, HOLE, TYPE 248D, PRESSURE SENST
10312738 IVE, ADHESIVE BACKING, 3M, 25 PER BOX, PEOPLE HAVE BEEN COMPLAINING ABOU
10312738 T, NORTON BRAND NOT STICKING GOOD ENOUGH., BUY 3M BRAND

Sorry about that primitive formatting (I also uploaded a sample workbook), but is there a way to do this without have to sort and do the function manually for all the materials? Some have descriptions that are 600 characters long. I was thinking this might need a macro and I am very inexperienced with these: The macro would count the characters in the description field and if there are more than 72 it would return the segmented values to another sheet and duplicate the material number with each piece of the description until it was all accounted for.

Help would very much appreciated since I have about 10000 line items to check this.
Thanks,

Jason

[NBVC]

See attached.

Added a couple of helper columns.

In C2:

Please Login or Register  to view this content.

copied down

in D2:

Please Login or Register  to view this content.

copied down

in E2:

Please Login or Register  to view this content.

In A11:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down as far as you need.

In B11:

Please Login or Register  to view this content.

confirmed with just ENTER and copied down.

[NBVC]

The above is for 70 characters (I misread)... just replace any 70 occurance in any formula with 72 for you actual allowance.

Attached revised sample

[jdaggie6]

Wow, I will work on that. So I just copy the formulas on down below my existing sheet with all 10000 parts and it will reference those?

Thanks again for the quick and helpful response.

Jason

[NBVC]

Yes it should... you can also put it on another sheet and make sure to reference the source sheet in the formula...

[jdaggie6]

I am sorry to bother you again but when I try to make this in a bigger sample and get it to place it on another sheet it will not let me. I really am an excel novice, but I could swear I am getting the references right and it still won't return any value. Here is what I have done.

[NBVC]

You have to put apostrophes around sheet names that are multiple words so in A2

Please Login or Register  to view this content.

make sure after you enter this formula, you hold the CTRL and SHIFT keys down and then press ENTER, then copy it down.

In B2 enter:

Please Login or Register  to view this content.

confirm as normal with ENTER only

[jdaggie6]

Forgot about the CTRL+SHIFT and ENTER keys. It worked, thanks for everything.

Jason