get 2nd word from cell if cell not empty, else first 9 letters of the word if there is no space

Akaur

I have

=IF([Source Detail]@row <> "", MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100))

what it is missing is if there is no 2nd word, then give me 9 letters from left of the word.

Above formula works when I have following word in Source Detail :

Source Detail : Testing This

Above formula doesn't work when I have following word in the Source Detail

Source Detail: Testingthis

I tried the following but doesn't work I get incorrect argument or invalid operator errors.

=IF(OR([Source Detail]@row <> " ", LEFT([Source Detail]@row, 9), [Source Detail]@row <> "", (MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100))))

=IF(OR([Source Detail]@row <> "", [Source Detail]@row <>" ", MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100))

There should be if this, give me this response, else give me this response.

I am missing the part that says, if it doesn't have "space" or 2nd word, then give me 9 letters of whatever is in there.

Your help is appreciated.

Find more posts tagged with

Formulas

Accepted answers

All comments

Mary_A

This does it

=IF(NOT(ISBLANK([Source Detail]@row)), IF(CONTAINS(" ", [Source Detail]@row), RIGHT([Source Detail]@row, LEN([Source Detail]@row) - FIND(" ", [Source Detail]@row)), LEFT([Source Detail]@row, 9)), "")

Check to make sure the cell contains a value. If it is blank, the formula returns nothing. (Might be more proper to drop the "" but it adds readability)
If the cell contains a value, check to see if it has a space. If it has a space, get the second word. (Note this solution assumes you never have a value like John Paul Jones in the Source Detail.)
If the cell has no space, get the left most 9. Shorter words like Test return Test --- doesn't try to get 9 chars if they aren't there

NOTE: A thank you to @Paul Newcome who did the LEN work on another question.

Akaur

I'm doing a happy dance 💃. Thank you thank you thank you!!!

This is simpler and better and more beautiful than what I came up with. I'm going to use your solution.

I created formula in three columns. One that gives me either the second letter (if "source" is something specific), and for all other "source" give me 9 letters from left, third to bring it all together. I got a "space" for two letter words. I used "Substitute". It's a bit disjointed.

1st column =IF(Source@row = "magazine", MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100), LEFT([Source Detail]@row, 10))

2nd column =SUBSTITUTE([word]@row, " ", "")

3rd column =IF(Source@row = "magazine", (LEFT([Source Detail]@row, 1) + (LEFT([word]@row, 4))), (substitute@row)

I love your solution @Mary_A

Sign in to join the conversation!

Quick Links

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.