creating and combining abbreviations from two words

New Contributor

I work with plants as part of my job and I handle databases of plant names, mostly in latin (ie. genus and species). I would like to be able to create a 7 letter capitalized abbreviation from a latin name in a given cell using the first 4 letters of the genus and combined with the first 3 letters of the species. For example, cell A1 is "Festuca campestris" and I would like cell B1 to result in "FESTCAM". I've looked online and get stumped by the complexity of nested functions using textjoin. Any help would be appreciated

2 Replies
best response confirmed by Grahmfs13 (Microsoft)


=UPPER(LEFT(A1,4)&MID(A1,FIND(" ",A1)+1,3))

You can try this formula which seems to work in my sheet.


Thank you! I figured this would be easy for someone out there but was beyond me, greatly appreciated!