Find words starting with capital letters

Hello,

I have a column (Species) where I have some species names e.g. Pseudomonas uno, Bacillus due, Vibrio tre - in the same cell. I want to create another column where I only have the info of the words with capital letter (Genus) filtered i.e. Pseudomonas, Bacillus, Vibrio. Right now I have a formula that allows me to get the first one = =IF(FIND(" ", Species@row) > 0, LEFT(Species@row, FIND(" ", Species@row) - 1)). Is there a way to filter by capital letters to get also the other three words in my new column?

Thanks!

Silvia

Find more posts tagged with

Accepted answers

All comments

Nick Korna

Hi @Silvia Brochet,

If your Species column is only ever 1-2 words long then this should work for you:

=IFERROR(LEFT(Species@row, FIND(" ", Species@row) - 1) + " " + UPPER(MID(Species@row, FIND(" ", Species@row) + 1, 1)) + RIGHT(Species@row, LEN(Species@row) - FIND(" ", Species@row) - 1), Species@row)

Sample output:

Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

Silvia Brochet

https://community.smartsheet.com/discussion/comment/433213#Comment_433213

Hello @Nick Korna,

Thank you for your answer. I would like to keep only the first word (the only one that is in capital) in my formula column. So it would be like this:

Is this possible?

Thanks,

Silvia

Nick Korna

Yes - but it gets complicated depending on how many different species are listed on a row. Is there a limit of how many species could be listed in a given row? And would they always have something to separate them (the comma would work in this case)?

Silvia Brochet

I am not sure I can define a limit of how many species are listed, I guess up to ten if I have to make a rough estimate. They would always have the comma separating them, yes. Thanks for your help!

Silvia

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.