Transform Data

Hello, sorry for the limited title description, but I was at a loss for how to actually describe what I am looking to do.

I have a workbook that has been kept for over 10 years and I would like to transform this data into a bit more usable form. In short, for any row, I have a number, which is currently a combination of an integer and a letter (either B, L or M). I would like to separate these, so the integer remains in the "#" column, while the letters are added to a separate column (for each letter). Alternatively, if this is easier, the letter could be just added to a single column.

An example is attached.

Columns A through E are what the format currently looks like. The options above that I described are H through L or N through P. The "Compound" column can be almost anything and there are many duplicates that should remain separate.

Thanks in advance for all your help.

Try this:

I2 =LEFT(B2,LEN(B2)-1)+0
Drag down

J2 =LEFT(B2,LEN(B2)-1)+0
Drag to the right and down

If you wanted to do it the other way, you can do this:

O2 =LEFT(B2,LEN(B2)-1)+0
Drag down

P2 =RIGHT(B2,1)
Drag down

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.