I have data that is multiresponse in a cell, separated by ALT+ENTER (or ctrl + j).
The three categories that can be in a single cell are "Unreleased", "Released", or "Released for Testing"
I want to add a column that's "contained released", meaning that it filters for the cells that contain "Released" ALONE, not released for testing or unreleased.
Example data:
+------------+---------+-----------+----------------------+
| Company ID | Tech ID | Tech Name | Tech Status |
+------------+---------+-----------+----------------------+
| 123 | 333 | soft 3 | Unreleased |
| | 112 | soft 2 | Released for Testing |
+------------+---------+-----------+----------------------+
| 456 | 223 | soft 1 | Released |
| | 112 | soft 2 | Released for Testing |
+------------+---------+-----------+----------------------+
| 789 | 119 | soft 4 | Released for Testing |
| | 223 | soft 1 | Released |
| | 333 | soft 3 | Unreleased |
+------------+---------+-----------+----------------------+
For the above data, I'd want to retrieve companies 456 and 789 because the "Tech Status" column for those records contain "Released"
I don't know how to do this without getting the data for both "Unreleased" and "Released for Testing".
This is exported data, and I cannot choose how the data looks or exports. It's all an XML spreadsheet and I have no idea how to change XML. But, I can change it just like a regular spreadsheet.
I was thinking maybe I can use a search function with "Released" & Char(13) but I don't know how to get rid of the "Unreleased" & Char(13) retrieval. Unless there's a way I can make sure nothing comes before that line. Maybe a substring function combined?
If the word is case consistent - i.e. always Released, never released or RELEASED, then a simple formula like:
=IF(ISNUMBER(FIND("Released"&CHAR(10),A1&CHAR(10))),"contained released","")
would do. If it isn't, then wrap the A1 in a PROPER(A1) function:
=IF(ISNUMBER(FIND("Released"&CHAR(10),PROPER(A1)&CHAR(10))),"contained released","")