Excel formula for extracting prefixes

By Posted on

QUESTION :

I have a table of 2 columns – file name & PC ID.
What I need is list of all 3-letter prefixes – for each PC ID separate. For example:

For:

BUSstop1  PC1
BUSstop3  PC2
TRMstop8  PC1
BIKnet1   PC1

I want to see something like this:

PC1:
BUS
TRM
BIK

PC2:
BUS

ANSWER :

Add the formula =LEFT(A1,3) to column C.

Then, create a pivot table based on the data in the three columns. First drag the field with the PCs in the row field, then the field with the calculated 3 letter codes.

Done! 🙂