Creating a part number from an item description

**yr25** · 07-10-2013, 04:46 PM

I would like to create a part number based off a short description:

Is there a formula I can use to pull the first letter or number of each word in a cell and then concatenate them into one word into another cell.

Column C has a brief description
Column D is empty but I would like to have a part number based off the data in column C

For example on Row 1, column C: 8 IN. PNEUMATIC WHEELS - I would like column D to show 8IPW
For example on Row 2, column C: FOOT BREAK OPTION - I would like column D to show FBO

I know there's a way but not sure how to do accomplish this. Thank you in advance for looking into this.

YR

**tigeravatar** · 07-10-2013, 04:58 PM

YR,

Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in.
I recommend using my ConcatAll UDF

Use the code in Post 8 of that thread which is the most up to date code.
In the first post of that same thread are instructions for how to implement it.

I have attached an example workbook that contains the UDF.
Row 1 is a header row so actual data starts in row 2.
In column C is the description
In column D is the part number which is obtained with this formula in cell D2 and copied down:

Please Login or Register  to view this content.

Does that work for you?

**yr25** · 07-11-2013, 11:31 AM

Tigeravatar, your excel knowledge is well beyond me. Your result is exactly what I need. I tried looking at the ConcatAll UDF and I'm not clear on the steps on how to implement so this function code will work. I have never used VBA so I need a little hand holding if you have the time or is there another method that is a little more work but still a time saver? I use Excel 2010

Thank you again for the time you have spent on this so far.

Thanks - YR

**tigeravatar** · 07-11-2013, 11:53 AM

YR,

Attached is a non-vba version that can accomplish the same thing. It uses several helper columns, which you can cut/paste to a different sheet or simply hide those columns.
In the attached workbook, the helper columns start in column H and use this formula in cell H2 copied over and down:

Please Login or Register  to view this content.

You can copy it farther right if some of your descriptions will have more than 4 words.
Then in cell D2 and copied down is a simple formula that joins all of the helper columns together:

Please Login or Register  to view this content.

**yr25** · 07-11-2013, 12:02 PM

Tigeravatar, bless you this was much easier for me to handle and it works!!

I noticed some of the new created part numbers happen to be duplicate, is there a way to isolate those dups in a particular column so I can make them unique. I just need them highlighted or a separate column pointing to the dup and I can manually fix them to make those few unique.

Thank you again for solving this

YR

**tigeravatar** · 07-11-2013, 12:13 PM

In your profile it shows you as using Excel 2010. You can select the part number columns and then go to Conditional Formatting -> Highlight Cells Rules -> Duplicate Values. That will highlight all duplicates so that they are easy to spot.