This is a problem I am faced with often so I thought it would be a good topic for my first Quick Tip Tuesday!
In our lab, we have many different machines that use multi-well plates (from 6 to 384 well format). On these plates, the wells are labelled in a grid pattern with letters and numbers (B5, G11, etc)
The problem comes when we retrieve the data from whichever machine is processing the plate. Depending on the software, often the data is just supplied in a long table with the well coordinates down one column. In order to be able to sort and filter by both column and row, we need to separate out the letter (row designation) from the number (column designation). This is easy to do with a couple of Excel tricks.
Firstly, you need your well designations to run down a column so if they start running across a row, you will need to copy all the data then paste transpose into a new sheet (right-click in cell A1 of the new sheet and select the Transpose option).
Now, insert two new columns between your well designations and your data. Give them headings of Row and Column.
In cell B2 (adjust accordingly if your layout differs from mine) enter the formula:
This takes one character from the left of the text in A2, ie: the letter designating the row.
In cell C2 enter the formula:
This one is slightly more complicated because the column number can be either 1 or 2 digits so just taking a single character from the right won’t work for columns 10 and above. LEN(A2)-1 works out the length (in characters) of A2 and subtracts 1 thus giving the required number of characters to designate the column.
Select cells B2 and C2 and drag the handle down to fill those formulas into all rows.
Now you can filter or sort your data by either row or column as necessary. Note: the numbers designating the column are stored as text at this stage. If you try to sort by them Excel will ask if you want to sort anything that looks like a number as a number, select this option and they will sort numerically as expected.
Has this helped you? Do you need help with your specific layout? Leave a comment below!