Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
up vote 1 down vote accepted

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! :-)

share|improve this answer
    
Thanks, didn't even know about pivot tables. – mikelowa Feb 28 '13 at 2:09
    
They're by far the most powerful feature in Excel! Play around a bit - and in future you'll use it for every analyses that involve more than 10 rows and 2 columns of source data, I'll promise! – Peter Albert Feb 28 '13 at 7:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .