Google Sheets - display only one of three words that might be in a cell?

I realize that this might be a long shot considering how limited Google sheets seems to be but let's see if there are any Google sheets gurus on EE. :)

I have a sheet that is importing selected cells from another sheet that is populated by a Google Form, using the following formula:
=IMPORTRANGE("https://docs.google.com/spreadsheets/xxxxx","Form Responses 1!A2:A200")

But this column contains more than one answer, separated by a comma. It may appear like this: "3, N/A" or "5, Live" or "3, 4, Self"
However, I only wish to display the text and not the number values. Is there a way to filter and display only the three possible text results?

Thank you
LVL 4
S ConnellyTechnical WriterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Do you mean that the cells in the column will contain one of the values from the list "3, N/A" or "5, Live" or "3, 4, Self"?
0
S ConnellyTechnical WriterAuthor Commented:
Hello Norie,

If the cell, from the remote spreadsheet contains, "3, N/A" then display "N/A" only. So only look for and display either "N/A", "Live", or "Self" and ignore all the other content in the cell.

Thanks
0
NorieAnalyst Assistant Commented:
Shawn

I don't know if it's possible to do what you want directly with IMPORTRANGE, have you considered using a 'helper' column?

That helper column could use a simple text formula to extract the text.

PS How does the form populate column A on 'Form Responses 1'?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

S ConnellyTechnical WriterAuthor Commented:
Hi Norie,

The form populates the original exactly like I showed. Example, "3, Self" or some variation of that.

It is fine if I must do this in two steps... i.e. a helper column. I think you mean, put the importrange on another column ("hidden") and then show the displayed row with formula based on the helper column.
0
S ConnellyTechnical WriterAuthor Commented:
Basically, I am just looking for a formula that allows me to extract specific words from a cell. I will try to make it work with what I have.

For instance, =RIGHT(D9, SEARCH(",",D9)+2) will not work because it is possible that the cell will only contain the text or the cell may contain more than one comma. I only want to show a the three possible text conditions and ignore all else.

Thank you
0
NorieAnalyst Assistant Commented:
Shawn

If the cells will only contain one of the values "3, N/A" or "5, Live" or "3, 4, Self" then I can't see why a simple formula like the one you posted won't work but you seem to be indicating that might not be the case.

What other values could be in the cells?
0
S ConnellyTechnical WriterAuthor Commented:
The formula I posted returns an error if the cell doesn't contain a comma.

The cells could be any combination of commas, numbers, and text but I am only interested is extracting any one of three specific words (as stated above).
0
NorieAnalyst Assistant Commented:
I suppose you could use nested IFs, though it doesn't seem to be an ideal solution.

=IF(ISNUMBER(SEARCH("N/A", A1)), "N/A",IF(ISNUMBER(SEARCH("LIVE", A1)), "LIVE",IF(ISNUMBER(SEARCH("SELF", A1)),"SELF",A1)))
0
S ConnellyTechnical WriterAuthor Commented:
Thanks Norie, that works pretty good. I "hide" the IMPORTRANGE columns and used your suggested formula in the visible columns.

Question: Your formula will display a number from the hidden column in the event that none of the three words are not in the cell. What would be required to not display anything if none of the words are present?
0
NorieAnalyst Assistant Commented:
Not sure why you are seeing a number when there's no match, you should see the original value.

Anyway if you want a blank if there's no match try this.

=IF(ISNUMBER(SEARCH("N/A", A1)), "N/A",IF(ISNUMBER(SEARCH("LIVE", A1)), "LIVE",IF(ISNUMBER(SEARCH("SELF", A1)),"SELF","")))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
S ConnellyTechnical WriterAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.