Find words starting with capital letters

Silvia Brochet

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

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