1

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?

P.b
  • 8,293
  • 2
  • 10
  • 25
hanbanan
  • 81
  • 5
  • 3
    Is the word case consistent - i.e. always `Released`, never `released` or `RELEASED`? If so, 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. – Rory Feb 24 '22 at 15:32
  • @Rory it is always consistent, I'll try this out edit: think this one worked, thank you! – hanbanan Feb 24 '22 at 15:36
  • Nice solution on adding the `&CHAR (10)` behind `A1` to account for `Released` being the last Tech Status. @Rory, you should post as answer. – P.b Feb 24 '22 at 17:11

1 Answers1

1

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","")
Rory
  • 32,730
  • 5
  • 32
  • 35