Shorten contents of spreadsheet cells

I have a spreadsheet file with a list of species’ Latin names. I want to automatically create abbreviations for each species, by shortening both the genus and specific epithet to a certain number of characters. Here’s how to do it.

Let’s say I have these species:

Alligator_mississippiensis
Anas_platyrhynchos
Anolis_carolinensis
Bison_bison
Bos_taurus
Callithrix_jacchus
Camelus_bactrianus

When I paste them in Gnumeric, I select the underscore “_”, as a custom separator in the Text Import Configuration box. This places the two parts of the names into individual columns (“A” and “B”):

I want to take the first 3 characters of “A” and the first 4 characters of “B”, to make the abbreviation in column “C”, connected by an underscore. This is done in the following way, using the LEFT function:

=left(A1,3)&"_"&left(B1,4)

Finally, to “restore” the two parts of the full latin name in a single column “D”, I used:

=(A1)&"_"&(B1)

That’s it!

This entry was posted in Academic, office and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s