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
Answers
-
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!
-
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
-
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)?
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!