Name Manipulation Tutorial
Scenario 1: The clone coordinates
are in a text format and plate row and column are separated
by a space. However the desired format is the one required
by bacpac online ordering.
Scenario 2: The clone names are in a text file and
need to be separated into their individual components:
library name, plate number, row, column.
Scenario 1: The clone coordinates are in a text
format and plate row and column are separated by a space.
1. Use Excel's data manipulation functions in order to combine
the different parts of the clone name.
a - paste your clones from notepad into excel
b - Select the column containing the clone information
c - open text to columns window from the data menu.
d - Select the delimited mode.
e - select Space as the delimiter.
Note: using this window, you could also separate a library
from a clone name, eg RP11-130G11
into RP11 and 130G11 (using "-" as the delimiter).
f - if a column needs to have a special format, select the
column and specify the format.
Note: if one of the destination columns contained a clone
coordinate, you would have to set
it as text since conflicts
could arise for clone names with column E, for instance,
mis-read a clone named 150E3 and interpret it as the number
150000 (to prevent this you
would also need to specify the target "cell format" as text
using the cells properties).
g - The text is now separated.
h - type in the formula using the
"&" operator. & acts as the + operator with
the difference that it operates on
and concatenates them together.
1 - The result is the full clone name.
Click and hold on the tiny square pointed to by the arrow
and drag down;
the mouse button.
Note: You could also double click the square
pointed to by the arrow.
j - The result is the list of clones
as expected. It can now be pasted directly into the online
clone ordering page.
Scenario 2: The clone names are in a text file and need
to be separated into their individual components: library
name, plate number, row, column.
a - if you start from a full clone name you can separate
library name from the actual clone coordinate using Scenario
1 e) and f).
b - The clone coordinates are in Microsoft Word.
c - Select Edit - Replace.
d - Click on the "More" button.
e - More advanced search options appear.
f - for the "Find What" box, select "Any Letter".
g - Select the "Replace With" Field.
h - Select repetitively the replace fields in the order
indicated in the picture (Tab Character
then Find What Text
then Tab Character).
Note: the tab character is necessary here because it will
allow easier pasting into Excel. When pasting
under excel, as tab character instructs excel to go to the
following cell (the cell to the right) and
an "end of line" character instructs excel to go to the beginning
of the next line.
i - Click the Replace All Button.
k - Here are the resulting split clone
names. These can be pasted in excel and further operations
done on this list,
in particular sorting of the clones.
k - to view the formatting in word click on the following
l - You can now see the formatting
in Word and be sure that pasting under excel will work as
the arrows indicate
that there is a tab character.
Note: In Excel a tab characters instructs Excel to go to
the following cell (to the right)